Re: find out a user/account permission...
From: Mark Allison (marka_at_no.tinned.meat.mvps.org)
Date: 04/22/04
- Next message: David Portas: "Re: CAL users - 1 or many?"
- Previous message: grumy: "mail error"
- In reply to: Steve Lin: "Re: find out a user/account permission..."
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 22 Apr 2004 20:01:27 +0100
I attached it as a file as it's not technically a binary, and also the
formatting gets preserved. Newsreader software tends to chop text, and that
spoils the aesthetics of the code. :-)
Anyway, I've pasted it below for your convenience.
select
quotename(o.name) AS ObjectName
, case o.type
when 'p' then 'Procedure'
when 'u' then 'Table'
when 'tr' then 'Trigger'
when 'c' then 'Constraint'
when 'tf' then 'Function'
when 'v' then 'View'
else 'Unknown' end as ObjectType
, quotename(u.name) AS DBUserName
, sum(case when action = 195 and protecttype in (204,205) then 1 -- GRANTED
when action = 195 and protecttype = 206 then -1 -- REVOKED
else 0 end) as 'INSERT' -- neither
, sum(case when action = 193 and protecttype in (204,205) then 1
when action = 193 and protecttype = 206 then -1
else 0 end) as 'SELECT'
, sum(case when action = 197 and protecttype in (204,205) then 1
when action = 197 and protecttype = 206 then -1
else 0 end) as 'UPDATE'
, sum(case when action = 196 and protecttype in (204,205) then 1
when action = 196 and protecttype = 206 then -1
else 0 end) as 'DELETE'
, sum(case when action = 224 and protecttype in (204,205) then 1
when action = 224 and protecttype = 206 then -1
else 0 end) as 'EXECUTE'
, sum(case when action = 26 and protecttype in (204,205) then 1
when action = 26 and protecttype = 206 then -1
else 0 end) as 'REFERENCES'
, sum(case when action = 198 and protecttype in (204,205) then 1
when action = 198 and protecttype = 206 then -1
else 0 end) as 'CREATE TABLE'
, sum(case when action = 203 and protecttype in (204,205) then 1
when action = 203 and protecttype = 206 then -1
else 0 end) as 'CREATE DATABASE'
, sum(case when action = 207 and protecttype in (204,205) then 1
when action = 207 and protecttype = 206 then -1
else 0 end) as 'CREATE VIEW'
, sum(case when action = 222 and protecttype in (204,205) then 1
when action = 222 and protecttype = 206 then -1
else 0 end) as 'CREATE PROCEDURE'
, sum(case when action = 228 and protecttype in (204,205) then 1
when action = 228 and protecttype = 206 then -1
else 0 end) as 'BACKUP DATABASE'
, sum(case when action = 233 and protecttype in (204,205) then 1
when action = 233 and protecttype = 206 then -1
else 0 end) as 'CREATE DEFAULT'
, sum(case when action = 235 and protecttype in (204,205) then 1
when action = 235 and protecttype = 206 then -1
else 0 end) as 'BACKUP LOG'
, sum(case when action = 236 and protecttype in (204,205) then 1
when action = 236 and protecttype = 206 then -1
else 0 end) as 'CREATE RULE'
from
sysprotects p
inner join sysobjects o on p.id = o.id
inner join sysusers u on p.uid = u.uid
where objectproperty (o.id, 'IsMsShipped') = 0
group by o.name, o.type, u.name
order by o.type, o.name, u.name
-- Mark Allison, SQL Server MVP http://www.markallison.co.uk "Steve Lin" <lins@nospam.portptld.com> wrote in message news:uW9ZfuHKEHA.2808@TK2MSFTNGP11.phx.gbl... > can you resend the query w/o using attachement? > my company remove any email attachment from outside automatically. (or if > you can rename the file and change the file extension to .port and resend > it?) > thank you > > > "Mark Allison" <marka@no.tinned.meat.mvps.org> wrote in message > news:uR$LaZFKEHA.2784@TK2MSFTNGP10.phx.gbl... > > The attached query should work for you, it is based around sysprotects. > > > > -- > > Mark Allison, SQL Server MVP > > http://www.markallison.co.uk > > > > > > > > > > "Steve Lin" <lins@nospam.portptld.com> wrote in message > > news:edcL0R$JEHA.428@TK2MSFTNGP11.phx.gbl... > > > is there an easy to find out an user/account's permsions within a > > database? > > > thank you > > > a list of objects and rights. > > > such as > > > > > > select delete insert exec > > > table1 x x > > > table2 x > > > stored proc x > > > > > > > > > > > > > >
- Next message: David Portas: "Re: CAL users - 1 or many?"
- Previous message: grumy: "mail error"
- In reply to: Steve Lin: "Re: find out a user/account permission..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
