Who has select / execute permissions on objects

Question

Who has select / execute permissions on objects

Loundy

SSCertifiable

Points: 6080
More actions
May 1, 2012 at 3:00 am

#260643

Hi All,
Does anyone have a nifty script or a scripted way to see what permissions a login (windows) has on objects? I need to identify select permissions on columns/views and execute permissions on stored procedures.
Cheers all
[font="Times New Roman"]There's no kill switch on awesome![/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12317 More actions · Answer 1

I don't have a script to identify the rights one specific user/login has, but I do have this script that scripts out all rights defined per object (if run by a user with sufficient access rights).

--select '-- Warning: You do not have ''dbo'' rights in this database, you probably can not see all database privileges.'

--where user_id('dbo') is null

select

case when nullif( 'dbo', grantee.name) is null then '--' else '' end +

case dbpm.state

when 'D' then 'DENY'

when 'R' then 'REVOKE'

when 'G' then 'GRANT'

when 'W' then 'GRANT'

else 'unknown state ' + isnull(dbpm.state, '<null>')

end + ' ' +

isnull(dbpm.privileges, 'NULL') + ' ' +

case dbpm.class

when 0 /* Database */ then ''

when 1 /* Object or Column */ then 'ON ' + isnull( quotename(object_schema_name(dbpm.major_id)) + '.', '') + isnull(quotename(object_name(dbpm.major_id)), 'null') + ' '

when 3 /* Schema */ then 'ON SCHEMA::' + quotename(schema_name(dbpm.major_id)) + ' '

when 4 /* Database principal */ then 'ON ' + case dbp.type

when 'R' then 'ROLE'

when 'A' then 'APPLICATION ROLE'

else 'USER'

end + '::' + quotename(dbp.name) + ' '

when 5 /* Assembly */ then 'ON ASSEMBLY::' + quotename(asm.name) + ' '

when 6 /* Type */ then 'ON TYPE::' + isnull(quotename(schema_name(typ.schema_id)) + '.', '') + quotename(typ.name) + ' '

when 10 /* XML Schema Collection */ then 'ON XML SCHEMA COLLECTION::' + isnull( quotename(schema_name(xsc.schema_id)) + '.', '') + isnull(quotename(xsc.name), 'null') + ' '

when 15 /* Message type */ then 'ON MESSAGE TYPE::' + quotename(smt.name) + ' '

when 16 /* Service contract */ then 'ON CONTRACT::' + quotename(sc.name) + ' '

when 17 /* Service */ then 'ON SERVICE::' + quotename(svc.name) + ' '

when 18 /* Remote Service Binding */ then 'ON REMOTE SERVICE BINDING::' + quotename(rsb.name) + ' '

when 19 /* Route */ then 'ON ROUTE::' + quotename(rout.name) + ' '

when 23 /* Full Text Catalog */ then 'ON FULLTEXT CATALOG::' + quotename(ftc.name)

when 24 /* Symmetric Key */ then 'ON SYMMETRIC KEY::' + quotename(symk.name)

when 25 /* Certificate */ then 'ON CERTIFICATE::' + quotename(cer.name)

when 26 /* Asymmetric Key */ then 'ON ASYMMETRIC KEY::' + quotename(asymk.name)

else 'unsupported class ' + convert(varchar(36), dbpm.class) + ' '

end +

case dbpm.state when 'R' then 'FROM ' else 'TO ' end + isnull(quotename( grantee.name), 'null') +

case dbpm.state when 'W' then ' WITH GRANT OPTION' else '' end +

isnull(' AS ' + quotename( grantor.name), '') +

';'

from (

select

x.grantee_principal_id,

x.class,

x.major_id,

x.state collate database_default [state],

x.grantor_principal_id,

(

select

case row_number() over (order by priv.description) when 1 then '' else ',' end +

priv.description as [text()]

from (

select

p.permission_name as [description]

from sys.database_permissions p

where p.minor_id = 0

and p.class = x.class

and p.major_id = x.major_id

and p.state = x.state

and p.grantee_principal_id = x.grantee_principal_id

and p.grantor_principal_id = x.grantor_principal_id

union all

select

p.permission_name + '(' +

(

select

case row_number() over (order by pcol.minor_id) when 1 then '' else ',' end +

col_name( pcol.major_id, pcol.minor_id) as [text()]

from sys.database_permissions pcol

where pcol.class = p.class

and pcol.major_id = p.major_id

and pcol.state = p.state

and pcol.grantee_principal_id = p.grantee_principal_id

and pcol.grantor_principal_id = p.grantor_principal_id

and pcol.permission_name = p.permission_name

for xml path('')

) + ')'

as [description]

from sys.database_permissions p

where not p.minor_id = 0

and p.class = x.class

and p.major_id = x.major_id

and p.state = x.state

and p.grantee_principal_id = x.grantee_principal_id

and p.grantor_principal_id = x.grantor_principal_id

group by

p.grantee_principal_id,

p.class,

p.major_id,

p.state,

p.grantor_principal_id,

p.permission_name

) priv

for xml path('')

) as privileges

from sys.database_permissions x

group by

x.grantee_principal_id,

x.class,

x.major_id,

x.state,

x.grantor_principal_id

) dbpm

left outer join sys.database_principals grantee on (grantee.principal_id = dbpm.grantee_principal_id)

left outer join sys.database_principals grantor on (grantor.principal_id = dbpm.grantor_principal_id and grantor.name <> 'dbo')

left outer join sys.database_principals dbp on (dbpm.class = 4 and dbp.principal_id = dbpm.major_id)

left outer join sys.assemblies asm on (dbpm.class = 5 and asm.assembly_id = dbpm.major_id)

left outer join sys.types typ on (dbpm.class = 6 and typ.user_type_id = dbpm.major_id)

left outer join sys.xml_schema_collections xsc on (dbpm.class = 10 and xsc.xml_collection_id = dbpm.major_id)

left outer join sys.service_message_types smt on (dbpm.class = 15 and smt.message_type_id = dbpm.major_id)

left outer join sys.service_contracts sc on (dbpm.class = 16 and sc.service_contract_id = dbpm.major_id)

left outer join sys.services svc on (dbpm.class = 17 and svc.service_id = dbpm.major_id)

left outer join sys.remote_service_bindings rsb on (dbpm.class = 18 and rsb.remote_service_binding_id = dbpm.major_id)

left outer join sys.routes rout on (dbpm.class = 19 and rout.route_id = dbpm.major_id)

left outer join sys.fulltext_catalogs ftc on (dbpm.class = 23 and ftc.fulltext_catalog_id = dbpm.major_id)

left outer join sys.symmetric_keys symk on (dbpm.class = 24 and symk.symmetric_key_id = dbpm.major_id)

left outer join sys.certificates cer on (dbpm.class = 25 and cer.certificate_id = dbpm.major_id)

left outer join sys.asymmetric_keys asymk on (dbpm.class = 26 and asymk.asymmetric_key_id = dbpm.major_id)

order by

dbpm.class,

dbpm.major_id,

dbpm.state

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

Don't have a query to hand, but there's sys.database_permissions, any use?

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

Gazareth

One Orange Chip

Points: 27737

May 1, 2012 at 3:19 am

#1481650

Crikey that's a script!

Loundy SSCertifiable Points: 6080 More actions · Answer 4

R.P.Rozema (5/1/2012)
I don't have a script to identify the rights one specific user/login has, but I do have this script that scripts out all rights defined per object (if run by a user with sufficient access rights).
--select '-- Warning: You do not have ''dbo'' rights in this database, you probably can not see all database privileges.'
--where user_id('dbo') is null
select
case when nullif( 'dbo', grantee.name) is null then '--' else '' end +
case dbpm.state
when 'D' then 'DENY'
when 'R' then 'REVOKE'
when 'G' then 'GRANT'
when 'W' then 'GRANT'
else 'unknown state ' + isnull(dbpm.state, '<null>')
end + ' ' +
isnull(dbpm.privileges, 'NULL') + ' ' +
case dbpm.class
when 0 /* Database */ then ''
when 1 /* Object or Column */ then 'ON ' + isnull( quotename(object_schema_name(dbpm.major_id)) + '.', '') + isnull(quotename(object_name(dbpm.major_id)), 'null') + ' '
when 3 /* Schema */ then 'ON SCHEMA::' + quotename(schema_name(dbpm.major_id)) + ' '
when 4 /* Database principal */ then 'ON ' + case dbp.type
when 'R' then 'ROLE'
when 'A' then 'APPLICATION ROLE'
else 'USER'
end + '::' + quotename(dbp.name) + ' '
when 5 /* Assembly */ then 'ON ASSEMBLY::' + quotename(asm.name) + ' '
when 6 /* Type */ then 'ON TYPE::' + isnull(quotename(schema_name(typ.schema_id)) + '.', '') + quotename(typ.name) + ' '
when 10 /* XML Schema Collection */ then 'ON XML SCHEMA COLLECTION::' + isnull( quotename(schema_name(xsc.schema_id)) + '.', '') + isnull(quotename(xsc.name), 'null') + ' '
when 15 /* Message type */ then 'ON MESSAGE TYPE::' + quotename(smt.name) + ' '
when 16 /* Service contract */ then 'ON CONTRACT::' + quotename(sc.name) + ' '
when 17 /* Service */ then 'ON SERVICE::' + quotename(svc.name) + ' '
when 18 /* Remote Service Binding */ then 'ON REMOTE SERVICE BINDING::' + quotename(rsb.name) + ' '
when 19 /* Route */ then 'ON ROUTE::' + quotename(rout.name) + ' '
when 23 /* Full Text Catalog */ then 'ON FULLTEXT CATALOG::' + quotename(ftc.name)
when 24 /* Symmetric Key */ then 'ON SYMMETRIC KEY::' + quotename(symk.name)
when 25 /* Certificate */ then 'ON CERTIFICATE::' + quotename(cer.name)
when 26 /* Asymmetric Key */ then 'ON ASYMMETRIC KEY::' + quotename(asymk.name)
else 'unsupported class ' + convert(varchar(36), dbpm.class) + ' '
end +
case dbpm.state when 'R' then 'FROM ' else 'TO ' end + isnull(quotename( grantee.name), 'null') +
case dbpm.state when 'W' then ' WITH GRANT OPTION' else '' end +
isnull(' AS ' + quotename( grantor.name), '') +
';'
from (
select
x.grantee_principal_id,
x.class,
x.major_id,
x.state collate database_default [state],
x.grantor_principal_id,
(
select
case row_number() over (order by priv.description) when 1 then '' else ',' end +
priv.description as [text()]
from (
select
p.permission_name as [description]
from sys.database_permissions p
where p.minor_id = 0
and p.class = x.class
and p.major_id = x.major_id
and p.state = x.state
and p.grantee_principal_id = x.grantee_principal_id
and p.grantor_principal_id = x.grantor_principal_id
union all
select
p.permission_name + '(' +
(
select
case row_number() over (order by pcol.minor_id) when 1 then '' else ',' end +
col_name( pcol.major_id, pcol.minor_id) as [text()]
from sys.database_permissions pcol
where pcol.class = p.class
and pcol.major_id = p.major_id
and pcol.state = p.state
and pcol.grantee_principal_id = p.grantee_principal_id
and pcol.grantor_principal_id = p.grantor_principal_id
and pcol.permission_name = p.permission_name
for xml path('')
) + ')'
as [description]
from sys.database_permissions p
where not p.minor_id = 0
and p.class = x.class
and p.major_id = x.major_id
and p.state = x.state
and p.grantee_principal_id = x.grantee_principal_id
and p.grantor_principal_id = x.grantor_principal_id
group by
p.grantee_principal_id,
p.class,
p.major_id,
p.state,
p.grantor_principal_id,
p.permission_name
) priv
for xml path('')
) as privileges
from sys.database_permissions x
group by
x.grantee_principal_id,
x.class,
x.major_id,
x.state,
x.grantor_principal_id
) dbpm
left outer join sys.database_principals grantee on (grantee.principal_id = dbpm.grantee_principal_id)
left outer join sys.database_principals grantor on (grantor.principal_id = dbpm.grantor_principal_id and grantor.name <> 'dbo')
left outer join sys.database_principals dbp on (dbpm.class = 4 and dbp.principal_id = dbpm.major_id)
left outer join sys.assemblies asm on (dbpm.class = 5 and asm.assembly_id = dbpm.major_id)
left outer join sys.types typ on (dbpm.class = 6 and typ.user_type_id = dbpm.major_id)
left outer join sys.xml_schema_collections xsc on (dbpm.class = 10 and xsc.xml_collection_id = dbpm.major_id)
left outer join sys.service_message_types smt on (dbpm.class = 15 and smt.message_type_id = dbpm.major_id)
left outer join sys.service_contracts sc on (dbpm.class = 16 and sc.service_contract_id = dbpm.major_id)
left outer join sys.services svc on (dbpm.class = 17 and svc.service_id = dbpm.major_id)
left outer join sys.remote_service_bindings rsb on (dbpm.class = 18 and rsb.remote_service_binding_id = dbpm.major_id)
left outer join sys.routes rout on (dbpm.class = 19 and rout.route_id = dbpm.major_id)
left outer join sys.fulltext_catalogs ftc on (dbpm.class = 23 and ftc.fulltext_catalog_id = dbpm.major_id)
left outer join sys.symmetric_keys symk on (dbpm.class = 24 and symk.symmetric_key_id = dbpm.major_id)
left outer join sys.certificates cer on (dbpm.class = 25 and cer.certificate_id = dbpm.major_id)
left outer join sys.asymmetric_keys asymk on (dbpm.class = 26 and asymk.asymmetric_key_id = dbpm.major_id)
order by
dbpm.class,
dbpm.major_id,
dbpm.state

BINGO! Excellent script many thanks - this will save me a tonne of time!! all I needed to do was add EXECUTE AS USER = at the start to see what that user had access too.

Cheers for this

[font="Times New Roman"]There's no kill switch on awesome![/font]

Who has select / execute permissions on objects

Cookies on SQLServerCentral