Level of access SYSADMIN has across databases

Ed Hall 41 Reputation points
2020-10-01T15:56:29.077+00:00

Want to confirm my understanding of access rights is correct in SQL Server.

Situation: Server TXB001 hosting databases DB01, DB02, and DB03. User1 has SYSADMIN rights to Server TXB001 and DB_Owner role to DB01, DB_datareader to DB02, and no role assigned to DB03. However, because User1 has SYSADMIN rights on TXB001, his rights on the databases are irrelevant; since they are hosted on that server he has DBO permissions to all three databases.

Just for background I am not a SQL Server user, just a person trying to confirm assumptions prior to discussing this with a client. I want to make sure I understand how it works before I potentially get people worked up. Thank you in advance.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,301 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Phillips 17,711 Reputation points
    2020-10-01T18:06:10.45+00:00

    It is not possible to restrict anything a user in the "sysadmin" can do. No other setting matters. A user assigned to the "sysadmin' server role, bypasses all security checks and can do everything on the server and all databases.

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

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Guoxiong 8,126 Reputation points
    2020-10-01T16:35:40.23+00:00

    Since User1 is a member of sysadmin role, it can do anything within the SQL server.

    0 comments No comments

  2. Erland Sommarskog 99,296 Reputation points MVP
    2020-10-01T21:16:57.077+00:00

    However, because User1 has SYSADMIN rights on TXB001, his rights on the databases are irrelevant; since they are hosted on that server he has DBO permissions to all three databases.

    Yes, this is correct.

    0 comments No comments