fixed server roles a user (login) has

  • 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?

  • 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

    ---------------------------------------------------------------------

  • Interesting approach. This gives me something to work with. Thanks for the script George. I appreciate it.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply