Header

Monday, October 10, 2011

How to document logins with server roles

Recently a customer asked me if I could write a script that could document which logins on the sql SQL Server that was member of a server role.

Select
CASE
WHEN isntgroup=1 THEN 'Windows Group'
WHEN isntuser=1 THEN 'Windows User'
WHEN isntuser=0 THEN 'SQL Login '
END LoginType,
loginname as [Login Name],
CASE sysadmin WHEN 1 THEN 'Yes' ELSE 'No' END isSysAdmin,
CASE securityadmin WHEN 1 THEN 'Yes' ELSE 'No' END isSecurityAdmin,
CASE serveradmin WHEN 1 THEN 'Yes' ELSE 'No' END isServerAdmin,
CASE processadmin WHEN 1 THEN 'Yes' ELSE 'No' END isProcessAdmin,
CASE diskadmin WHEN 1 THEN 'Yes' ELSE 'No' END isDiskAdmin,
CASE dbcreator WHEN 1 THEN 'Yes' ELSE 'No' END isDBCreator,
CASE bulkadmin WHEN 1 THEN 'Yes' ELSE 'No' END isBulkAdmin,
createdate LoginCreatedDate
from sys.syslogins
where hasaccess=1

No comments: