![]() ![]() Once you’ve decided who the SQL Server database owner should be and you’ve identified those databases not owned by that account, let’s look at a couple of ways you can change to database owner. It’s not a perfect solution, but it’s consistent, clear, and meets the company and compliance needs in most cases. We recommend having sa own all databases. Others contend that sa should own the databases. Some advocate creating a specific disabled account to own all databases. Now that you know who your SQL Server database owners are, the next question is: who should own your databases? Get-DbaDatabase -SqlInstance localhost -SqlCredential sa | Where-Object | Format-Table -Property Name, Owner Who Should Own Your Database For readability, I pass the results through Format-Table. In the code below I pipe the results to a Where-Object to show only those databases not owned by sa. Find the database owner using PowerShell and dbatools.ioįor fans of PowerShell and dbatools, the Get-DbaDatabase command can be used to determine the database owner. In my example, I’d get the following results in an ADS window. Or, if you prefer, you can limit the results to show only those databases not owned by a specific user, such as sa. Using your SQL Server query tool of choice, SSMS or ADS, run the following T-SQL query to see the owner for every database attached to this instance of SQL Server. So let’s look at two programmatic ways to check the database owner. Pretty simple for a single database, but very cumbersome for a few dozen or a few hundred databases spread across dozens of SQL Servers. In this example, the BaseballData database is owned by the Joe login. Under the Database heading, you’ll see its owner. Using either SQL Server Management Studio (SSMS) or Azure Data Studio (ADS) drill down to the database, right click on it, and select Properties to open the following window. One of the easiest ways to determine the database owner is to view its properties. Find the database owner using Database Properties So, how can you tell if this may have happened in your environment? Or put another way, how can you tell who the database owner is for a database? Let’s look at three ways. So the power user that created the database still owns it, even though he doesn’t work on the CMO’s team any longer. Except, no one really thought about the database owner during the transfer. ![]() Eventually the team doesn’t want to “own” the responsibility for it any longer so it’s handed over to the DBA team for updates, maintenance, backups, HA/DR planning, licensing, etc. Over time, the database grows and becomes more important. Take, for example, the Chief Marketing Officer who authorizes her team to build a database to track their activities. So how do individual users end up becoming SQL Server database owners? In fact, this is one of the common findings we uncover during our SQL Assessments for customers. Yet, it’s not uncommon for individual users to own specific and important databases. In highly secure environments (and what environment shouldn’t be classified as highly secure these days?) all of these permissions can create headaches for auditors, CISOs, and even DBAs. The database owner can even drop the database altogether. The SQL Server database owner can change configuration parameters, perform maintenance, and grant permissions on the database to other users. That means they have elevated permissions on the database. In SQL Server, when someone creates a database, they own it. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |