Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Server Role Membership Validation Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2014 12:07 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 9:20 PM
Points: 879, Visits: 2,474
Comments posted to this topic are about the item Server Role Membership Validation

Shawn Melton
PS C:\>(Find-Me).TwitterURL
@wsmelton
PS C:\>(Find-Me).BlogURL
meltondba.wordpress.com
Post #1582035
Posted Tuesday, June 17, 2014 10:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, June 19, 2014 9:15 PM
Points: 131, Visits: 124
These system procedures are helpful, too (sp_helpsrvrole and sp_helpsrvrolemember).
Post #1582477
Posted Thursday, June 19, 2014 4:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:56 PM
Points: 6,365, Visits: 13,695
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"
Post #1583683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse