locked
Does SA of the server has SA priviliges also in SQL Server installed in the server? RRS feed

  • Question

  • if someone is defined as SA in the server,
    does he automatically receive SA priviliges in the installed SQL Server instance
    or he should be first defined as a Login in SQL Server by the SA of the SQL Server?


    Geri Reshef http://gerireshef.wordpress.com

    Monday, March 12, 2012 6:51 PM

Answers

  • sa permission on SQL Server should be granted in order to be sysadmin on the installed Instance of the SQL Server.
    • Marked as answer by Geri_Reshef Monday, March 12, 2012 7:18 PM
    Monday, March 12, 2012 7:13 PM
  • That depends on the version of SQL Server.

    On SQL 2005, BUILTIN\Administrators are by default granted access to the server in the sysadmin role. You need to explicitely remove the role.

    On SQL 2008 it's the other way round. When you install SQL Server, you need to specify a Windows user that is to be sysadmin, but you choose whatever you like, and that user does not need to have any particular privileges in Windows.

    However, beware that when SQL Server is started in single-user mode by a Windows administrator that person will have sysadmin permissions in SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Geri_Reshef Tuesday, March 13, 2012 1:25 AM
    Monday, March 12, 2012 10:31 PM

All replies

  • sa permission on SQL Server should be granted in order to be sysadmin on the installed Instance of the SQL Server.
    • Marked as answer by Geri_Reshef Monday, March 12, 2012 7:18 PM
    Monday, March 12, 2012 7:13 PM
  • That depends on the version of SQL Server.

    On SQL 2005, BUILTIN\Administrators are by default granted access to the server in the sysadmin role. You need to explicitely remove the role.

    On SQL 2008 it's the other way round. When you install SQL Server, you need to specify a Windows user that is to be sysadmin, but you choose whatever you like, and that user does not need to have any particular privileges in Windows.

    However, beware that when SQL Server is started in single-user mode by a Windows administrator that person will have sysadmin permissions in SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Geri_Reshef Tuesday, March 13, 2012 1:25 AM
    Monday, March 12, 2012 10:31 PM