SQL Server: Change Database Owner to SA


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 '

&nbsp;
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s