SQL Server: Change Database Owner to SAPosted: August 28, 2013
In SQL Server, when a database is created, the owner will be the user who created it.
This ownership gives the creator extra permissions which could cause potential security issues.
Another important issue is when the user (network user id) is not working at organization any more
and we don’t want this user to be the database owner obviously.
1.Find database owner:
We can find the owner of a database like this with T-SQL:
SELECT name, suser_sname(owner_sid) FROM sys.databases
2. Change database owner manually
We can change the owner of the database to sa from SSMS:
Right-click on database name>>Properties>>Files>>Owner>>Browser to find sa and clcik OK.
If we want to change all databases’ owner to sa in one step, you can run the following T-SQL script:
3.Change all user databases owner with T-SQL:
---Tested on SQL Server 2008 sp_msforeachdb ' USE [?] IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''distribution'' BEGIN ALTER AUTHORIZATION ON DATABASE::[?] to sa; END '