SQL Server Roles

Опубликовано: 31 Октябрь 2024
на канале: Afterowl

337

How do you know what roles a user account have.

SELECT *
FROM (
SELECT LoginName = p.name
,AccountType = p.type_desc
,BulkAdmin = CASE WHEN IS_SRVROLEMEMBER('bulkadmin', p.name) = 1 THEN 'bulkadmin' END
,DBCreator = CASE WHEN IS_SRVROLEMEMBER('dbcreator', p.name) = 1 THEN 'dbcreator' END
,DiskAdmin = CASE WHEN IS_SRVROLEMEMBER('diskadmin', p.name) = 1 THEN 'diskadmin' END
,ProcessAdmin = CASE WHEN IS_SRVROLEMEMBER('processadmin', p.name) = 1 THEN 'processadmin' END
,[Public] = CASE WHEN IS_SRVROLEMEMBER('public', p.name) = 1 THEN 'public' END
,SecurityAdmin = CASE WHEN IS_SRVROLEMEMBER('securityadmin', p.name) = 1 THEN 'securityadmin' END
,ServerAdmin = CASE WHEN IS_SRVROLEMEMBER('serveradmin', p.name) = 1 THEN 'serveradmin' END
,SetupAdmit = CASE WHEN IS_SRVROLEMEMBER('setupadmin', p.name) = 1 THEN 'setupadmin' END
,SysAdmin = CASE WHEN IS_SRVROLEMEMBER('sysadmin', p.name) = 1 THEN 'sysadmin' END
FROM sys.server_principals p
WHERE TYPE IN ('U', 'S', 'G')
AND p.name NOT LIKE '%##%'
)Data
WHERE SysAdmin IS NOT NULL
ORDER BY LoginName, AccountType