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:
Post a Comment