SQL 2016 SA account - Backup database

Question

Good Morning Colleagues

I hope you are doing good . Regarding SQL server 2016 , Does the  SA account has the permissions to backup database ?

Best Regards

Answer 1

What a question … the build-in "sa" account is the system administrator account with unlimited permissions; of course it has also permissions to backup databases

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Good Morning Olaf

Thx for your confirmation . Appreciate find my below comments and waiting for your confirmation for it:

1. I think SA account can do also restore for the database . is that true ?
2. My question related to SQL server 2016  and i got information from one of my colleagues that SA account @ SQL 2016 doesn't have unlimited permissions and cant do database back up and it needs to have db_backuuoperator role attached to it to be able to backup/restore . So kindly advice.

Thx in advance.

Best Regards

Answer 3

As they say: it depends. On a regular instance, sa can do anything. Which of course includes backup and restore of databases. The only limitation is that you can not read or write the backup anywhere, but the service account for SQL Server needs to have permission on the destination/source for the backup. This particularly matters if you are working with a file share. But here we are outside the realm of SQL Server.

Now, at some sites they disable and/or rename sa so the name it something else. And if they are really evil they may create a regular login called sa. I have never heard of the latter, but I would not be surprised if someone would do this.

If you are logged in as sa, you can run SELECT suser_sid(). If it returns 0x01, this is indeed the almighty sa, but if is something else, someone has done this trick.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Good Afternoon Erland 

Thx for your detailed replay but i got information from one of my colleagues that SA account @ SQL 2016 doesn't have unlimited permissions and can't do database back up and it needs to have db_backuuoperator role attached to it to be able to backup/restore . So kindly advice. 

Thx in advance.

Best Regards

Answer 5

Your colleagues are wrong. ...unless they have played the trick with renaming the original sa account and created a new account with that name as I discussed in my previous post.

Did you try running the SELECT that I had in my post?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

Hi Erland 

Thx for your confirmation . I don't have access to SQL 2016 database . Do you have any document from Microsoft listing the permissions of SA in SQL 2016 ?

Best Regards

Answer 7

Seems like your colleagues are pulling your legs!

You can download and install SQL Server Express 2016 (or later) version to play with this on your own.

As for documentation, I guess it is somewhere in Books Online, let's see where is that bookmark to Google? Oh, there it is. What about https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 8

Hi Erland

Thanks for the shared document . I checked it and it was great . I need to share with you the below document also:

Since i am new to this forum , I cant share the link of the document but if you google using the statement -Getting Started with Database Engine Permissions- , This will be the first result . if you cant reach it , Tell me to send it to you via email.

Under the topic "List of permissions" , you will find a very good PDF file with all servers permissions and database permissions . Based on the document you shared ,  SA is part of sysadmin role . In the PDF chart , You will find all permissions under sysadmin role where db_backupoperator role - which responsible for backup - is not under sysadmin role which means that SA account - which is under sysadmin role - can't backup database .

Appreciate to check PDF file and give me your advice.

Thx in advance

Best Regards

Answer 9

SQL Server permissions can be complex. The PDF you referenced is quite useful for better understanding the entire permissions hierarchy but can be confusing too.

This documentation states sysadmin permission more simply: "members of the sysadmin fixed server role can perform any activity in the server." There are no exceptions because permissions for sysadmin role members not checked. One cannot revoke or deny permissions to sysadmin role members.

And this doc page calls out (my emphasis): "The sysadmin fixed server role encompasses all other roles and has unlimited scope. Do not add principals to this role unless they are highly trusted. sysadmin role members have irrevocable administrative privileges on all server databases and resources."

 

---

Dan Guzman, Data Platform MVP, http://www.dbdelta.com

Answer 10

Under the topic "List of permissions" , you will find a very good PDF file with all servers permissions and database permissions . Based on the document you shared ,  SA is part of sysadmin role . In the PDF chart , You will find all permissions under sysadmin role where db_backupoperator role - which responsible for backup - is not under sysadmin role which means that SA account - which is under sysadmin role - can't backup database .

In the big graph for database permissions you find CONTROL SERVER in the upper left corner, and this leads to CONTROL DATABASE and from there you can go to db_backupoperator.

Furthermore, if you are sa or member of the sysadmin fixed server role, you map to dbo in all databases. dbo is short for db_owner and is the almighty user on database level.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Hi Erland

Thx so much for your support friend.

Best Regards