locked
"SysAdmin" server role RRS feed

  • Question

  • Hello,

    I am reading that the "Sysadmin" server role can perform any activity on the server.

    I am almost new to SQL-Server and was wondering if this means that the Sysadmin can access any database or I should place the user in a database-level role such as "db_owner" ?

    Thanks in advance!


    Luis Olías Técnico/Admon Sistemas . Sevilla (España - Spain)

    • Moved by Olaf HelperMVP Sunday, December 7, 2014 4:34 PM Moved from "Database Engine" to a more related forum.
    Wednesday, December 3, 2014 5:09 PM

Answers

  • SysAdmin role will override the other privileges. 

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Luis Olias Wednesday, December 3, 2014 5:56 PM
    Wednesday, December 3, 2014 5:33 PM
  • Hi Luis,

    If user has sysadmin role, no matter what other roles are assigned. User can do anything with Sysadmin.

    If you just want to give read access then you must give public access on server level.

    Please Mark This As Answer if it solved your issue 
    Please Mark This As Helpful if it helps to solve your issue

    _____________________________________

    Thanks,

    Shashikant

    • Marked as answer by Luis Olias Wednesday, December 3, 2014 5:56 PM
    Wednesday, December 3, 2014 5:36 PM
  • If you are sysadmin, you map do dbo in any database. That is, you are member of the db_owner role in every database. All permission checks are voided.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Luis Olias Wednesday, December 3, 2014 11:27 PM
    Wednesday, December 3, 2014 10:08 PM

All replies

  • Sysadmin technically can access any database on that instance. If you want to limit the access of a specific user to one database and have also permission to do anything with that database you can give that user db_Owner permission or be more specific like db_DataReader or db_DataWriter. 

    db_owner

    Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

    Database-Level Roles

    db_ddladmin

    Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

    db_datawriter

    Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

    db_datareader

    Members of the db_datareader fixed database role can read all data from all user tables.


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Wednesday, December 3, 2014 5:13 PM
  • Thanks Arbi.

    But if the user has the Sysadmin server-level role and the db_datareader database-level role, what can the user do ? , Could he still write in that database because he is the Sysadmin ?


    Luis Olías Técnico/Admon Sistemas . Sevilla (España - Spain)


    • Edited by Luis Olias Wednesday, December 3, 2014 5:23 PM
    Wednesday, December 3, 2014 5:23 PM
  • SysAdmin role will override the other privileges. 

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Luis Olias Wednesday, December 3, 2014 5:56 PM
    Wednesday, December 3, 2014 5:33 PM
  • Hi Luis,

    If user has sysadmin role, no matter what other roles are assigned. User can do anything with Sysadmin.

    If you just want to give read access then you must give public access on server level.

    Please Mark This As Answer if it solved your issue 
    Please Mark This As Helpful if it helps to solve your issue

    _____________________________________

    Thanks,

    Shashikant

    • Marked as answer by Luis Olias Wednesday, December 3, 2014 5:56 PM
    Wednesday, December 3, 2014 5:36 PM
  • I see.

    Thanks both a lot !!


    Luis Olías Técnico/Admon Sistemas . Sevilla (España - Spain)

    Wednesday, December 3, 2014 5:57 PM
  • If you are sysadmin, you map do dbo in any database. That is, you are member of the db_owner role in every database. All permission checks are voided.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Luis Olias Wednesday, December 3, 2014 11:27 PM
    Wednesday, December 3, 2014 10:08 PM