May 1, 2012 at 3:00 am
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 🙂
May 1, 2012 at 3:15 am
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
May 1, 2012 at 3:16 am
Don't have a query to hand, but there's sys.database_permissions, any use?
May 1, 2012 at 3:19 am
Crikey that's a script! 😀
May 1, 2012 at 3:28 am
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 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply