SQL Server SYSADMIN account and DBO Role

Question

SQL Server SYSADMIN account and DBO Role

Ed Hall 41

Hello, My understanding is that when a SQL DB is created the SYSADMIN role is assigned the DBO role. My question is, if SYSADMIN has DBO formally removed and is assigned another role later, like db_datareader, does it still retain DBO level permissions in the database?

Accepted answer

2 additional answers

Your answer

Answer 1

Hi @Ed Hall ,

Hello, My understanding is that when a SQL DB is created the SYSADMIN role is assigned the DBO role. My question is, if SYSADMIN has DBO formally removed and is assigned another role later, like db_datareader, does it still retain DBO level permissions in the database?

Yes. It does. Becasue it is one member of SYSADMIN role. One user who is SYSADMIN, in other words, if one who has SYSADMIN permission, he can perform any activities in this server.

In your case, app-user and DBO all members of SYSADMIN, so ther is no use to change roles at database level.

Note: It is generally not recommended to directly assign server roles to users, because server roles are global, which means that you have server-level permissions. It is generally recommended to assign databases to users and then assign database role permissions to the corresponding databases. Do not add the database role created by the user to the fixed server database role, otherwise it will cause the fixed database role to be upgraded.

Next, let us clarify the server-level role and database-level role.

More information:permissions-hierarchy-database-engine, sql-server-create-user

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Erland Sommarskog 121.4K MVP Volunteer Moderator

Any user who is member of the sysadmin role maps to the dbo user in all databases.

The opposite does not apply. If a non-privileged login owns a database, that login maps to dbo in that database, and has full powers in that database, but does not have sysadmin permission.

A database user can also be member of the db_owner role, and then has permissions in the database on equal footing with dbo.

I am not sure that I understand your question. If you are sysadmin, you are dbo in all databases - and that is never going to change.

Ed Hall 41 Reputation points

2020-09-18T22:23:02.95+00:00

Thank you, it turns out I made a mistake in my question. In my scenario (acct names made up) the database has a username DBO with a user login of Domain\svc-admin. When I review the server accounts, Domain\svc-admin is a SYSADMIN. But there is another account, app-user, that has the SYSAMIN role, and when I look in the database there is no app-user account noted. (I confused another account and thought it was). So what I am understanding is regardless, the app-user account still has dbo permissions.
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2020-09-19T08:22:05.907+00:00

As I said, if you are member of sysadmin, you map to dbo in all databases.

I must say that it does not sound well with an account called app-user which is member of sysadmin. I don't know, maybe it is only called app-user to confuse, but if it is really used by an application to log on to the server, this is something you should aim at to get fixed. Ideally, an application should only have permission to run stored procedures. Not all applications uses stored procedures, and in such case it may be acceptable to grant the application account SELECT, INSERT, UPDATE and DELETE permissions. But anything beyond that is not acceptable from a security perspective.

If you take out an account of the sysadmin role, it will no longer have access to the database, if no user has been created for it.

Answer 3

m 4,276

Hi @Ed Hall ,

Is the reply helpful?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Share via

SQL Server SYSADMIN account and DBO Role

2 additional answers

Your answer