June 17, 2014 at 12:07 am
Comments posted to this topic are about the item Server Role Membership Validation
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
June 17, 2014 at 10:56 am
These system procedures are helpful, too (sp_helpsrvrole and sp_helpsrvrolemember).
June 19, 2014 at 4:09 am
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