« Resources for complex, enterprise SQL Server Implementations
SQL Server 2005 Partition Management Tool »

Which login has what server role permissions

Below script will query sys.server_role_members / sys.server_principals to find out which logins have what server role access.

Code:

SELECT 'ServerRole' = CASE
WHEN rm.role_principal_id > 2 THEN SUSER_NAME(rm.role_principal_id) END,
lgn.name AS 'MemberName',
'Login Create Date'=Convert(VARCHAR(24),Create_Date,126)
FROM sys.server_role_members AS rm RIGHT OUTER JOIN
sys.server_principals AS lgn ON rm.member_principal_id = lgn.principal_id
WHERE (lgn.principal_id <= 1 OR lgn.principal_id > 10) AND lgn.type <> 'C'
ORDER BY ServerRole DESC, MemberName

You could download it here

CU
tosc

SQL Server 2005 Books Online Scoped Search for: sys.server_role_members
sys.server_principals


Qui tacet, consentire videtur

sql server, sys.server_principals, sys.server_role_members, t-sql
SQL Server
 Print article This entry was posted by Torsten Schuessler on 2008-01-31 at 20:46:23 . Follow any responses to this post through RSS 2.0.
No feedback yet
Leave a comment

Your email address will not be revealed on this site.
PoorExcellent
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)
This is a captcha-picture. It is used to prevent mass-access by robots.
Please enter the characters from the image above. (case sensitive)
Trackback address for this post
This is a captcha-picture. It is used to prevent mass-access by robots.
Please enter the characters from the image above. (case sensitive)

Tag cloud

backup «best practices» books ctp «cumulative update» datetime denali dmv ebook «failover cluster» humor i/o index indexoptimize integrity links loginproperty maintenance «ms sql server 2008» performance php profiler «reporting services» reviews «ross mistry» rtm serverproperty «service pack» «service pack 3» sharepoint sp_msforeachdb «sql 2012» «sql pass» «sql server» «sql server 2005» «sql server 2008 r2» «sql server 2012» «sql server builds» sqlcat «sqlpass franken» ssms ssmstoolspack «stacia misner» t-sql technet «technical note» tools troubleshooting whitepapers «windows server 2003»