sysadmin vs serveradmin

Question

Post reply

sysadmin vs serveradmin

mak101

Ten Centuries

Points: 1111
More actions
October 26, 2012 at 2:41 pm

#263796

I am confused between sysadmin role and serveradmin role. According to books online sysadmin member can perform any activity in the server. Can anyone please clerify, which activity can not be performed by member of serveradmin?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

nothing like getting it straight from the source, and testing it yourself.

BooksOnline has a page about what each of the server roles allows:

http://msdn.microsoft.com/en-us/library/ms175892(v=sql.90).aspx

play with this sample code below; this is just one example of the difference between sysadmin and serveradmin:

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;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 2

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).

edit: spelling

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

dineshvishe SSCrazy Points: 2308 More actions · Answer 3

server admin can log in windows but sys admin log only sql server.

Sys admin require person on files of server but server can do operation shared files.

dan-572483 SSCoach Points: 15870 More actions · Answer 4

I don't believe a serveradmin can query data in user databases unless given specific database level permissions to do so. This allows you to completely separate server admin from data admin.