Tasks which only a sysadmin can perform

Question

Tasks which only a sysadmin can perform

S_Kumar_S

SSCrazy Eights

Points: 9037
More actions
October 27, 2016 at 8:44 am

#400947

Hi All
I need to know what the the tasks which only a sysadmin can perform and cannot be performed by any of other role/roles. I have came across 3 such tasks:
1.To perform all tasks related to transnational replication, you need to be sysadmin
2. you cannot created linked server
3. you cannot create maintenance plan
The reason behind this question is that we have to downgrade the sysadmin role of a person and we need to know what tasks he won't be able to perform if this is done.
Thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 1

By default, a user is in the PUBLIC role and cannot do anything. You then should grant them permissions piecemeal. There are probably 1,000 things a member of SYSADMIN can do that a PUBLIC user can't, and the list changes with each release of SQL Server.

The following query will (basically, I think) return a list of high level permissions. I'd say use this list to have a conversation with the user and then both of you decide what they need to perform as part of their job role.

SELECT DISTINCT parent_class_desc, parent_covering_permission_name FROM fn_builtin_permissions(default);

For example:

...

SERVERAUTHENTICATE SERVER

SERVERCONTROL SERVER

SERVERCREATE ANY DATABASE

SERVERCREATE DDL EVENT NOTIFICATION

SERVERVIEW ANY DEFINITION

SERVERVIEW SERVER STATE

...

DATABASEDELETE

DATABASEEXECUTE

DATABASEINSERT

DATABASEREFERENCES

DATABASESELECT

DATABASEUPDATE

DATABASEVIEW DEFINITION

...

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

Thanks for reply Eric but I actually wanted to know what are the tasks which can be performed by sysadmin only

like only a sysadmin can create a linked server. So what are other such tasks? Is there a way to figure that out?

thanks

Eric M Russell (10/27/2016)
By default, a user is in the PUBLIC role and cannot do anything. You then should grant them permissions piecemeal. There are probably 1,000 things a member of SYSADMIN can do that a PUBLIC user can't, and the list changes with each release of SQL Server.
The following query will (basically, I think) return a list of high level permissions. I'd say use this list to have a conversation with the user and then both of you decide what they need to perform as part of their job role.
SELECT DISTINCT parent_class_desc, parent_covering_permission_name FROM fn_builtin_permissions(default);
For example:
...
SERVERAUTHENTICATE SERVER
SERVERCONTROL SERVER
SERVERCREATE ANY DATABASE
SERVERCREATE DDL EVENT NOTIFICATION
SERVERVIEW ANY DEFINITION
SERVERVIEW SERVER STATE
...
DATABASEDELETE
DATABASEEXECUTE
DATABASEINSERT
DATABASEREFERENCES
DATABASESELECT
DATABASEUPDATE
DATABASEVIEW DEFINITION
...
...

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Eric M Russell SSC Guru Points: 125560 More actions · Answer 3

S_Kumar_S (10/27/2016)
Thanks for reply Eric but I actually wanted to know what are the tasks which can be performed by sysadmin only
like only a sysadmin can create a linked server. So what are other such tasks? Is there a way to figure that out?
thanks
...

The answer to your question is too long, but it's not all or nothing. Any administration type task a user needs to perform, from kicking off jobs to running profiler traces or backups, can be granted either directly to that user, to a role, or via a proxy account. For example, if a developer needs to query DMV, run profiler traces, or kick off jobs in production, you can grant them those specific permissions without granting them membership in SYSADMIN role.

GRANT Server Permissions

https://msdn.microsoft.com/en-us/library/ms186717.aspx

GRANT VIEW ANY DEFINITION ..

GRANT CREATE ANY DATABASE ..

GRANT ALTER TRACE ..

... etc.

SQL Server Agent Fixed Database Roles

https://msdn.microsoft.com/en-us/library/ms188283.aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Eric have given you great answers already. I don't know if there is a list of something like what you might be looking for as it's a bit different for a sysadmin. A sysadmin isn't really granted specific permissions. A sysadmin bypasses permission checks.

As already stated, there are numerous ways to allow permissions for different tasks. I'd say just bite the bullet, figure out what seems to be an appropriate role/permissions for the user and go from there, address things if needed.

Sue

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

S_Kumar_S (10/27/2016)
Hi All
I need to know what the the tasks which only a sysadmin can perform and cannot be performed by any of other role/roles. I have came across 3 such tasks:
1.To perform all tasks related to transnational replication, you need to be sysadmin
2. you cannot created linked server
3. you cannot create maintenance plan
The reason behind this question is that we have to downgrade the sysadmin role of a person and we need to know what tasks he won't be able to perform if this is done.
Thanks

You're actually asking the wrong question. You should be investigating what the person can still do. Perhaps surprising to you, even though you've removed the person from the SysAdmin server role, the person may still have SysAdmin privs and you'll never know it because you're not looking. You're just assuming. For the other people in the sysadmin role that don't know this, perhaps they should also be removed from the role because of their lack of knowledge about security. 😉

In theory, if you remove a person from the SysAdmin role, their privs should only be what is available to the PUBLIC role and whatever server roles, database roles, agent roles, and individual grants they've been given. The list of things that a person can do by role is available in Books Online and, as already stated, if far too extensive to post here.

I say "In theory" because there are privs that are realized through membership of any Active Directory Groups that are active in SQL Security that the person may be a part of. It IS possible that they may still have sysadmin privs if they are a member of an Active Directory Group that has such privs.

I strongly recommend you start a deep dive with xp_logininfo and other tools/system views in SQL Server to begin your search for what the person can actually do rather than what anyone thinks they might not be able to do.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 6

Thanks for an elaborated answer Jeff. I surely will look into the points you mentioned.

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

S_Kumar_S (10/28/2016)
Thanks for an elaborated answer Jeff. I surely will look into the points you mentioned.

You bet. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Karp-286289 SSC Enthusiast Points: 127 More actions · Answer 8

I don't think he's asking the wrong question. He's asking a very specific question, what are the things only a login in the sysadmin fixed server role can do. Does anyone know that?

Best Regards,

Matt Karp

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

Matt Karp-286289 wrote:

I don't think he's asking the wrong question. He's asking a very specific question, what are the things only a login in the sysadmin fixed server role can do. Does anyone know that?

To the best of my knowledge, the only way to create such a detailed list is to would be to search through every MS Doc article for the "Permissions" section for the given version and edition of SQL Server you intend to use. Even MS doesn't maintain such a comprehensive list that I know of. If you look at the following MS Doc, it implies that everything the other roles can't do, can be done by the sysadmin role.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15

The "best" way to handle sysadmin roles is to figure out what someone actually needs to do, look that up in BOL (MS Docs), and then figure out what permissions are necessary. Then decide that the given user doesn't actually need to have that high level of privs, write a stored procedure that does the task properly and safely, and then give the use privs to execute that stored procedure.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)