May 5, 2009 at 3:29 pm
I've tried Teh Google and BOL and I'm having trouble finding out how to do this: I want to get a list, result set, whatever, of what fixed server roles (ie. sysadmin, securityadmin, etc) a given userid/login has.
I've been able to get this using the backward compatibility view sys.syslogins but I want to move it to use... "regular" views/sprocs (not backward-compatible views).
According to the 2000-to-2005 mapping page in BOL, syslogins is replaced by server_principals and sql_logins. Unfortunately, neither tells me what server roles a user has.
There is a sproc, sp_helpsrvrolemember, that tells me what users have a particular role but I want to go the other way -- what roles does a user have.
Does anyone know a better way (besides the backward-compatible sys.syslogins view) to get this information?
May 5, 2009 at 3:38 pm
copied from this site at some time or other - cannot remember who's it was
/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SET NOCOUNT ON
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
+ QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1)
+ QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC
---------------------------------------------------------------------
May 5, 2009 at 3:51 pm
Interesting approach. This gives me something to work with. Thanks for the script George. I appreciate it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply