locked
SQL Server sysadmin rights RRS feed

  • Question

  • Hi,

    Can someone please guide which all rights would be missed on broad level if a person has sysadmin rghts vs all server roles minus sysadmin rights on a sql server 2008 r2.

    Thanks

    Wednesday, May 27, 2015 10:25 AM

Answers

  • Hello - There are several actions that explicitly require sysadmin membership and cannot be given by just combining & giving access to other all server roles for example many DBCC commands such as DBCC DROPCLEANBUFFERS requires you to be sysadmin and no other role can work.

    Additionally, there is a list of 171 system stored procedures (SQL Server 2012 as well as SQL Server 2008) that checks whether the current user is sysadmin or not.

    More info can be obtained here:

    http://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by VG2014 Wednesday, May 27, 2015 11:07 AM
    Wednesday, May 27, 2015 10:36 AM

All replies

  • Hello - There are several actions that explicitly require sysadmin membership and cannot be given by just combining & giving access to other all server roles for example many DBCC commands such as DBCC DROPCLEANBUFFERS requires you to be sysadmin and no other role can work.

    Additionally, there is a list of 171 system stored procedures (SQL Server 2012 as well as SQL Server 2008) that checks whether the current user is sysadmin or not.

    More info can be obtained here:

    http://www.insidesql.org/blogs/andreaswolter/2013/08/control-server-vs-sysadmin-sa-permissions-privilege-escalation-caveats


    Good Luck!
    Please Mark This As Answer if it solved your issue.
    Please Vote This As Helpful if it helps to solve your issue

    • Marked as answer by VG2014 Wednesday, May 27, 2015 11:07 AM
    Wednesday, May 27, 2015 10:36 AM
  • Cannot run xp_cmdshell  for example.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 27, 2015 10:40 AM
  • Hi,

    Can someone please guide which all rights would be missed on broad level if a person has sysadmin rghts vs all server roles minus sysadmin rights on a sql server 2008 r2.

    Thanks

    Why dont you search BOL for this information I am very sure no body remembers all such privileges.

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


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, May 27, 2015 10:41 AM