Basit's SQL Server Tips

It is all about passion!

Main menu

Skip to content
  • Home
  • SQL Server
  • SQL Server 2012
  • SQL Server 2014
  • SQL Server 2016
  • SQL Server Tips
  • SQL Server Performance Tips
  • SQL Server Encryption
  • SQL Server T-SQL Scripts
  • General Tips
  • Publications
  • About Me

Search

  • RSS Feed
  • Twitter
  • Facebook

Tag Archives: Changing db owner script

December 28, 2014

SQL Server 2014 Best Practice: Set built-in Sysadmin(sa) account as an default owner of all databases on SQL Server instance

As we know, when we create database on SQL Server instance, SQL Server engine sets the login that created the database as the default database owner. This gives the user full control on the database, meaning he/she can do whatever they like on that particular database. According to the SQL Server security best practice document, we should grant login with the least amount of privileges. Therefore, it is recommended to set built-in sa account as an owner of all databases on SQL Server instance. This will make database and server more secure.

How to identify the ownership of the system and user databases on SQL Server instance?

You can use SQL Server Management Studio to view the database owner. Follow these steps to view the database owner using SQL Server 2014 Management Studio:

  • In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine with Object Explorer.
  • In Object Explorer, right-click the database for which you want to check the database ownership, and then click Properties.
  • This opens the database property window, showing you the general database information and ownership details.

01

Alternatively, you can query sys.databases, as follows, to view the system and user databases on SQL Server instance:

02

As you can see, it is the quickest way to find the database owner.

How to change ownership of the user databases to sa?

To change the ownership of the user databases, you can use ALTER AUTHORIZATION Transact DCL command. The following example shows how you can change the ownership of AdventureWorks2012 database:

03

Note: Ownership of the system databases cannot be changed.

Below is the quick script I wrote, which can be used to identify the user database where the owner is not sa. This script also gives you ALTER AUTHORIZATION Transact DCL command for each database to transfer its ownership to sa account:

04

For more information, review SQL Server Security Best Practice white paper.

Rate this:

Share this:

  • Tweet
  • Email
  • Print

Like this:

Like Loading...
  • Posted in SQL Server Security
  • Tagged Changing db owner script, Changing DB ownership to sa account, Changing default database owner, Database Owner SA, Set SA as DBOwner, SQL Server 2014
  • 1 Comment

Post navigation

My Book

SQL Server 2014 Development Essentials (ISBN: 978-1782172550) is an easy-to-follow yet comprehensive guide that is full of hands-on examples. This book will provide you with all the skills you need to successfully design, build, and deploy databases using SQL Server 2014. Starting from the beginning, this book gives you an insight into the key stages of the SQL Server database process, provides you with an in-depth knowledge of the SQL Server database architecture, and shares tips to help you design the new database.

By sequentially working through the steps in each chapter, you will gain hands-on experience in designing, creating, and deploying SQL Server databases and objects. You will learn how to use SQL Server 2014 Management Studio and the advanced Transact-SQL queries to retrieve data from the SQL Server database. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how the SQL Server 2014 relation engine works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

Latest Tweets

  • The man third in the line of presidential succession has been in five 'Batman' movies cnn.com/2021/01/23/us/… 20 hours ago
  • The latest The SQL Server & Windows Daily! paper.li/alimcitp/13616… 20 hours ago
  • Password Monitor: Safeguarding passwords in Microsoft Edge microsoft.com/en-us/research… by @MSFTResearch 1 day ago
  • The latest The SQL Server & Windows Daily! paper.li/alimcitp/13616… Thanks to @GlennAlanBerry #intel #amd 1 day ago
  • Jobs in fintech bounce back after brief lockdown lull computerweekly.com/news/252494611… by @Karlfl #devops 2 days ago
  • The latest The SQL Server & Windows Daily! paper.li/alimcitp/13616… #devops 2 days ago
  • Handling failures when deploying to multiple databases with SQL Change Automation - Redgate Software… twitter.com/i/web/status/1… 3 days ago
  • The latest The SQL Server & Windows Daily! paper.li/alimcitp/13616… Thanks to @mssqltips @redgate #devops #databasedevops 3 days ago
  • Ten Reasons your SQL Server Monitoring will Scale - Redgate Software red-gate.com/hub/product-le… by @tonytheeditor 4 days ago
  • The latest The SQL Server & Windows Daily! paper.li/alimcitp/13616… Thanks to @GFritchey 4 days ago
Follow @BasitAali

Recent Posts

  • Quick Tip – How to fix SQL Server 2017 install error on Ubuntu 18.04: E: Could not get lock /var/lib/dpkg/lock-frontend – open (11: Resource temporarily unavailable)
  • CREATE DATABASE failed on SQL Server FCI cluster: Only formatted files on which the cluster resource of the server has a dependency can be used
  • How to change Integration Services Catalog (SSISDB) database Master Key encryption password?
  • Database Issue: The transaction log for database ‘db_name’ is full due to ‘ACTIVE_BACKUP_OR_RESTORE’.
  • An Overview on SQL Server Checkpoints

Top 5 Posts

  • Removing part of string before and after specific character using Transact-SQL string functions
  • Determining when statistics were last updated in SQL Server?
  • Find the size of Index in SQL Server
  • SQL Server: Converting binary data to a hexadecimal string
  • Availability databases in unhealthy data synchronization state (Error: 35285, Severity: 16, State: 1.)

Category Cloud

Encryption Failover Clustering (SQL Server) FileTable General Tips High Availability Linux MSSQLTips Packt Publishing Publications SQL-SERVER-PERFORMANCE.COM SQL Agent SQL Azure Database SQL Scripts SQL Server SQL Server 2012 SQL Server 2014 SQL Server 2016 SQL Server 2017 SQL Server 2019 SQL Server Builds SQL Server on Linux SQL Server Performance Tips SQLServerPro SQL Server Reporting Services SQL Server Security SQL Server Tips ssis SSWUG TechTarget Ubuntu

Click to subscribe to this blog and receive notifications of new posts by email.

Join 3,358 other followers

Copyright

All of the entries on this blog are copyright by Basit Farooq. The full content of any post (a post is an entry on the blog) may not be published elsewhere without prior permission from the copyright holder. Excerpts from blog posts may be posted elsewhere providing they are short (around 10% of the article) and are attributed to me by name and with a link back to this blog, currently located at the following address: http://basitaalishan.com.

The following sites currently have full permission to publish complete copies of posts that are published on this web blog:

Disclaimer

This is personal blog and opinions expressed here represent my own thoughts and not those of my employer. For accuracy and official references, refer to MSDN, Microsoft TechNet, Books Online. I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog. I've simply documented my personal experiences on this blog.

Blog Stats

  • 1,711,326 hits
Blog at WordPress.com.
Cancel
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
%d bloggers like this: