Tasks which only a sysadmin can perform

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

  • 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

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

  • 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

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

  • 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)
    Intro to Tally Tables and Functions

  • 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

  • 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)
    Intro to Tally Tables and Functions

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

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