Monday, January 25, 2016

SysAdmin vs. serveradmin role (sys is data admin while server is Operating system server admin

Here is the list of what each Server Role can do:

Permissions of Fixed Server Roles (2008 R2)

serveradmin can only do these things: ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE

Now cross-reference that list with the Server-level permissions in this article:

Permissions (2008 R2) > SQL Server Permissions

From this you can derive what serveradmin cannot do. If the securable is at the SERVER level (per fourth-column "Securable that contains base securable") and the last column ("Permission on container securable...") says CONTROL SERVER but does not list one of the items for serveradmin explicitly has (shown above) then it requires sysadmin. An example is ALTER ANY APPLICATION ROLE. It requires CONTROL SERVER and there is no other permission that can be granted to allow one to alter any application role. An example of where serveradmin overlap is with ALTER ANY ENDPOINT. Notice where the Base Securable (first column) is ENDPOINT there is a row where the last column is ALTER ANY ENDPOINT (a permission serveradmin explicitly has) as well a row where the Base Securable is CONTROL and the permission is CONTROL SERVER (a permission sysadmins have).


USE master;
CREATE LOGIN MyServerAdmin WITH PASSWORD=N'NotARealPassword', 
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

exec sp_addsrvrolemember MyServerAdmin,'serveradmin' 
--lets see what he can do!


--Change into my costume:
EXECUTE AS LOGIN = 'MyServerAdmin'
--well, the sysadmin can go to any database, and see stuff, drop stuff, etc.
--can i do it?

select * from SandBox.sys.tables
/*
Msg 916, Level 14, State 1, Line 1
The server principal "MyServerAdmin" is not able to access the database "SandBox" under the current security context.
*/
--take off the costume:
REVERT; 

--cleanup:
DROP LOGIN MyServerAdmin;

No comments:

Post a Comment