Database ownership and sa account

Question

I created a database as a Windows user belonging to the Administrator group. My authentication mode is mixed. Now I want to permanently use "sa" in my connection string and be able to alter, backup, restore the database. Do I need to give any special permissions to the sa account?

It seems like if I attach the DB as an "sa", the "sa" is the owner of the DB. Then does the "sa" automatically get all right to the database?

Answer 1

All permissions are assigned to sa, or members of sysadmin server level role.  No special permissions need to be assigned for sa, or any login belonging to sysadmin role, to backup or restore a database.  The problem with using this role is not the limitations of permissions, but rather limiting what activity a member of sysadmin can do.  This is am instance wide permission level so sysadmin's can do anything on the instance down to all objects.

 

Hope this helps

---

David Dye http://sqlsafety.blogspot.com/

Answer 2

Server level permissions are highest scope inside SQL server meaning that an login account (including sa) who is granted at SYSADMIN level at the SQL server level will have all possible permissions on all resources inside SQL server (all databases). Those sysadmin logins are mapped to each of the database through the DBO user in each of databases.

---

Thanks, Leks

Answer 3

<Those sysadmin logins are mapped to each of the database through the DBO user in each of databases.>

BTW, I had to change the database owner to 'sa' for the above stmt to be true.

As I mentioned in my first post, the database was created as an Admin user. I log in to SSMS as the Windows user (that created the DB) and then attach and detach the DB. But then I log in to SSMS as 'sa' and try to attach the same DB and I get an OS error saying that the file does not have access.

After checking the file permissions, sure enough, the security on the mdf and ldf files got modified ( do not know how) and were limited to my Windows user. After adding full control to SQLServerMSSQLUser$<my_computer_name>$<my_SQL_SERVER_instance_name>, only then was I able to attach the database as 'sa'.

If 'sa' has all the permissions, why am I seeing this behavior?

Answer 4

sa is a SQL Server login that is a member of the sysadmin fixed server role and has all the permissions that exist inside SQL Server. But to reach out and touch the files you needed Windows file system permissions. sa doesn't have a Windows identity.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 5

>>>>><Those sysadmin logins are mapped to each of the database through the DBO user in each of databases.>

BTW, I had to change the database owner to 'sa' for the above stmt to be true.>>>>>

You dont have to set the database ownership to sa to get the mapping done between sa and dbo - that mapping is existing by default for all sysadmin accounts

-- Login with sa andd run the below command
select system_user
go
select USER_NAME()
go

 

>>>> As I mentioned in my first post, the database was created as an Admin user. I log in to SSMS as the Windows user (that created the DB) and then attach and detach the DB. But then I log in to SSMS as 'sa' and try to attach the same DB and I get an OS error saying that the file does not have access.>>>

This error is most likely because your SQL server service account doesn't have access to those files on the disk/folder.

---

Thanks, Leks

Answer 6

Just as an observation:

I really dislike whenever the account "sa" is used. As much as windows people dislike whenever I login as the Windows account "Administrator".

---

Tibor Karaszi, SQL Server MVP | web | blog