Server Role Membership Validation

  • Comments posted to this topic are about the item Server Role Membership Validation

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • These system procedures are helpful, too (sp_helpsrvrole and sp_helpsrvrolemember).

  • i use the following

    SELECT sp.name AS ServerPrincipal,

    sp.type_desc AS LoginType,

    CASE sp.is_disabled

    WHEN 0 THEN 'No'

    WHEN 1 THEN 'Yes'

    END AS UserDisabled,

    sp.create_date AS DateCreated,

    sp.modify_date AS DateModified,

    sp.default_database_name AS DefaultDB,

    sp.default_language_name AS DefaultLang,

    sp2.name AS ServerRole,

    sper.permission_name AS Permission

    FROM sys.server_principals sp

    INNER JOIN sys.server_role_members sr ON sp.principal_id = sr.member_principal_id

    INNER JOIN sys.server_principals sp2 ON sr.role_principal_id = sp2.principal_id

    LEFT OUTER JOIN sys.server_permissions sper ON sp.principal_id = sper.grantee_principal_id

    where sp.name <> 'sa'

    ORDER BY ServerPrincipal, ServerRole

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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