August 22, 2007 at 1:37 pm
Anyone willing to share a script to dump all Users (preferrably from all DB's) and their respective permissions?
Thx in advance
August 23, 2007 at 5:56 am
Not sure if this is what you want, but this will give you all permissions granted to any userrole, user or public within a database. In order to get the information for all data you could use a cursor or sp_MSForeachDB.
Be aware that users which are member of a fixed role like datareader won't show.
select
RoleName,[ObjectName],[ObjectType],[EXECUTE],[SELECT],[INSERT],[UPDATE],[DELETE],[REFERENCES]
from
(select p.name as RoleName,(s.name + '.' + o.name) as [ObjectName],o.type as [ObjectType],
dp.permission_name,dp.state_desc -- Permissions granted on Objects
from sys.database_permissions dp
join sys.database_principals p on p.principal_id = dp.grantee_principal_id
join sys.objects o on dp.major_id = o.[object_id]
join sys.schemas s on o.[schema_id] = s.[schema_id]
where (p.type='R'OR p.type = 'U')
UNION
select p.name as RoleName,(s.name ) as [ObjectName],'Sch', -- Permissions granted on a schema
dp.permission_name,dp.state_desc
from sys.database_permissions dp
join sys.database_principals p
on p.principal_id = dp.grantee_principal_id
join sys.schemas s
on dp.major_id = s.[schema_id]
where (p.type='R'OR p.type = 'U')
and dp.class =3
)
p
PIVOT
(max(state_desc)
for permission_name in ([EXECUTE],[SELECT],[INSERT],[UPDATE],[DELETE],[REFERENCES])
) as pvt
order
by RoleName,[ObjectName]
Good luck
Markus
[font="Verdana"]Markus Bohse[/font]
August 23, 2007 at 6:12 am
Markus - thx for the SQL.. this worked fine in 2005. I modified the SQL to run against 2000 system tables and it's failing trying to get to sysdatabase_permissions (no such table). Would you happen to know the equivelant system table in 2000?
August 23, 2007 at 7:02 am
You posted your question in the 2005 Administering group so I assumed you're looking for a 2005 solution. In 2000 you need to query the syspermissions table.
Or what I usually use is a utilty called DumpSQLSec. You can find it here: http://www.sqlservercentral.com/columnists/cmiller/dumpsqlpermissions.asp
Markus
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply