thanks to this article for the base script. i needed to get a list of all users and the roles they had in each db. this script is great in that you can use the raw base script (mine is slightly modified) and list things for every object instead of just at the db level. in my case and in most people's case i imagine that makes this dataset rather large. this allowed me to get a large union on the raw data i needed for all of the databases and then i could put it one table so i could work with it to restructure a lot of permissions without having to requery over and over again. i don't have sa rights so this also gave me the ability to ask for sa rights briefly in order to accomplish this and then i could be taken off. if you had a less nice sa, this should give you the ability to give them something that only takes them like 10 minutes to do. =)
mostly like all things i'm just posting this so i don't forget it ha!
I just did this:
WITH perms_cte as( select USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc, p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc AS permission_state_desc from ?.sys.database_permissions p inner JOIN ?.sys.database_principals dp on p.grantee_principal_id = dp.principal_id where class_desc is null)--usersSELECT * INTO somewhere.DBO.R1_? FROM (SELECT [db]=db_name(), p.principal_name, p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_nameFROM perms_cte pWHERE principal_type_desc <> ''''DATABASE_ROLE''''UNION--role membersSELECT [db]=db_name(),rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_nameFROM perms_cte pright outer JOIN ( select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,* from ?.sys.database_role_members rm INNER JOIN ?.sys.database_principals dp ON rm.member_principal_id = dp.principal_id) rmON rm.role_principal_id = p.principal_id) X'''
Then ran the results to get the data for each db, then I ran this:
sp_msforeachdb 'print ''UNION ALL SELECT * FROM somewhere.DBO.R1_? '' '
and removed the first union and I had my data. Plus I have all the root tables in the somewhere database to work with temporarily.
posted @ Friday, September 05, 2008 7:19 PM