Skip to main content
Database Administrators

Return to Answer

Formatting
Source Link
LowlyDBA - John M
LowlyDBA - John M
  • 11k
  • 11
  • 45
  • 62

The following script lists logins that are members of the sysadmin server rôlerole and those having the CONTROL SERVER permission: SELECT
spr.name as loginName ,spr.type_desc ,spr.is_disabled ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN ,spm.permission_name ,spm.state_desc FROM
sys.server_principals spr inner join sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id WHERE
spm.permission_name = 'CONTROL SERVER' OR IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

SELECT   
     spr.name as loginName
    ,spr.type_desc
    ,spr.is_disabled
    ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN
    ,spm.permission_name
    ,spm.state_desc
FROM     
    sys.server_principals spr
    inner join
    sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id
WHERE    
    spm.permission_name = 'CONTROL SERVER'
    OR
    IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

The following script lists logins that are members of the sysadmin server rôle and those having the CONTROL SERVER permission: SELECT
spr.name as loginName ,spr.type_desc ,spr.is_disabled ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN ,spm.permission_name ,spm.state_desc FROM
sys.server_principals spr inner join sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id WHERE
spm.permission_name = 'CONTROL SERVER' OR IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1

The following script lists logins that are members of the sysadmin server role and those having the CONTROL SERVER permission:

SELECT   
     spr.name as loginName
    ,spr.type_desc
    ,spr.is_disabled
    ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN
    ,spm.permission_name
    ,spm.state_desc
FROM     
    sys.server_principals spr
    inner join
    sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id
WHERE    
    spm.permission_name = 'CONTROL SERVER'
    OR
    IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1
Source Link
Ali Ben Tahar
Ali Ben Tahar
  • 1

The following script lists logins that are members of the sysadmin server rôle and those having the CONTROL SERVER permission: SELECT
spr.name as loginName ,spr.type_desc ,spr.is_disabled ,(IS_SRVROLEMEMBER ('sysadmin',spr.name)) AS IS_SYSADMIN ,spm.permission_name ,spm.state_desc FROM
sys.server_principals spr inner join sys.server_permissions spm ON spm.grantee_principal_id = spr.principal_id WHERE
spm.permission_name = 'CONTROL SERVER' OR IS_SRVROLEMEMBER ('sysadmin',spr.name) = 1