October 16, 2008 at 5:46 am
Used to be, in SQL 2000, you could use sysusers, syslogins & sysmembers to map out the sql logins & window logins belonging to database roles. (IE, the below code)
DECLARE @SQLCmd VARCHAR(1024)
SELECT sid, loginname AS [Login Name], dbname AS [Default Database],
CASE isntname WHEN 1 THEN 'AD Login' ELSE 'SQL Login'
END AS [Login Type],
CASE WHEN isntgroup = 1 THEN 'AD Group' WHEN isntuser = 1 THEN 'AD User'
ELSE '' END AS [AD Login Type],
CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END AS [sysadmin],
CASE [securityadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [securityadmin],
CASE [serveradmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [serveradmin],
CASE [setupadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [setupadmin],
CASE [processadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [processadmin],
CASE [diskadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [diskadmin],
CASE [dbcreator] WHEN 1 THEN 'Yes' ELSE 'No' END AS [dbcreator],
CASE [bulkadmin] WHEN 1 THEN 'Yes' ELSE 'No' END AS [bulkadmin]
INTO ##Users
FROM dbo.syslogins
SELECT @SQLCmd = ' SELECT ''' + '(MyDB)' + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + '(MyDB)' + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN ##Users u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + '(MyDB)' + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + '(MyDB)' + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' '
EXEC (@SQLCmd)
These days, however, if we don't want to write code that we'll have to fix in a few years, we can no longer use these depreciated tables. Instead we have to use sys.database_principals, sys.sql_logins, sys.database_permissions and sys.database_role_members. None of which seem to have the flags we could use to determine if a SQL or Window login was a member of a specific database role. Even the code behind sp_helprole doesn't help. That proc lists the RoleName, the RoleID and if it's an AppRole. Not what members it has (contrary to what BOL says about the proc).
Per BOL, sp_helprole "Displays a list of the members of a fixed database role." which is now untrue.
This is frustrating. For SOX reasons, I need to redo my report on what roles have what members, but I can't seem to find this info without using depreciated system tables. Does anyone have any ideas?
BTW, run the above code in your favorite database to see what I'm searching for. You'll have to change (MyDB) to the name of your current database.
Thanks in advance.
October 16, 2008 at 6:28 am
Brandie,
I think this code is close to what you need:
[font="Courier New"]SELECT
DB_NAME() AS database_name,
MP.name AS database_user,
RP.name AS database_role,
SP.name AS server_login,
SPR.name AS server_role
FROM
sys.database_principals RP JOIN
sys.database_role_members R ON
RP.principal_id = R.role_principal_id JOIN
sys.database_principals MP ON
R.member_principal_id = MP.principal_id LEFT JOIN
sys.server_principals SP ON
MP.sid = SP.sid LEFT JOIN
sys.server_role_members SRM ON
SP.principal_id = SRM.member_principal_id LEFT JOIN
sys.server_principals SPR ON
SRM.role_principal_id = SPR.principal_id
[/font]
I know it isn't exactly what you want, but from what I know about you, I think this will get you started.
When you get it done can you submit it to the SSC script library?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 6:31 am
Oops, I think this is a little better:
[font="Courier New"]SELECT
DB_NAME() AS database_name,
MP.name AS database_user,
RP.name AS database_role,
SP.name AS server_login,
SPR.name AS server_role
FROM
sys.database_principals MP LEFT JOIN
sys.database_role_members R ON
MP.principal_id = R.member_principal_id LEFT JOIN
sys.database_principals RP ON
R.role_principal_id = RP.principal_id LEFT JOIN
sys.server_principals SP ON
MP.sid = SP.sid LEFT JOIN
sys.server_role_members SRM ON
SP.principal_id = SRM.member_principal_id LEFT JOIN
sys.server_principals SPR ON
SRM.role_principal_id = SPR.principal_id[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 16, 2008 at 6:37 am
Jack, you're a genius! And yes, as soon as I'm done fiddling and fixing to my satisfaction, I'll definitely post it to the library. @=)
Thanks!
October 16, 2008 at 11:45 am
Interestingly enough, it doesn't get granular on column permissions. Or rather, it does, but it doesn't properly reflect them.
I have a SQL Login which has been granted Select permissions on 5 columns out of a 20+ column table. When I run the following code, it shows SELECT has been granted on ALL the columns even though it really hasn't. I think this might be because I haven't actually specified a deny on the other columns, but haven't tested it yet.
Select so.name, sc.name, dp.name, p.class_desc, p.type,
p.permission_name, p.state, p.state_desc
from sys.columns sc
join sys.objects so
on sc.object_id = so.object_id
join sys.database_permissions p
on so.object_Id = p.major_Id
join sys.database_principals dp
on p.grantee_principal_id = dp.principal_id
where so.name = 'MyTable' and dp.name = 'MySQLLogin';
Of course, you'll have to substitute the table & login names for yourselves to make the code work properly.
Curious...
October 25, 2008 at 3:24 am
Submitted & waiting for approval. @=)
Though if I see you again at SQL Saturday, I'll tell you in person. @=)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy