Friday 19 April 2013

Difference between CONTROL SERVER and sysadmin membership in SQL Server

Difference between CONTROL SERVER permission and sysadmin fixed server role:
- logins with CONTROL SERVER permission are checked for explicit DENY of server level permissions
- members of sysadmin fixed server role bypass the check for explicit DENY of server level permissions.
To test this, we will grant test_permissions login CONTROL SERVER rights, and DENY CONNECT SQL for that login:
USE master;
GO
GRANT CONTROL SERVER TO test_permissions;
GO
DENY CONNECT SQL TO test_permissions;
GO

When test_permissions login attempts to connect, there will be the login failed message:

If we make the test_permissions login a member of the sysadmin fixed server role:
EXEC sp_addsrvrolemember 'test_permissions', 'sysadmin';
GO

…the login will be able to connect because the members of sysadmin fixed server role bypass the explicitly denied server level permission check.
To list the members of the sysadmin fixed server role, you can use the system stored procedure sp_helpsrvrolemember:
EXEC sys.sp_helpsrvrolemember 'sysadmin';
To list logins with CONTROL SERVER permission, you can use this code:
SELECT s.name
FROM sys.server_principals s
JOIN sys.server_permissions p
ON s.principal_id = p.grantee_principal_id
WHERE p.class = 100
AND p.type = 'CL'
AND (p.state = 'G'
OR p.state = 'W')

No comments:

Post a Comment