By: Manvendra Singh |Updated: 2021-04-29 |Comments | Related: > Security
Problem
We create, modify and drop databases, objects and other SQL Server relatedentities like logins, users, schemas, roles, endpoints, etc. as per our needsto address database objectives. Sometimes, some of the database activities stop workingand start throwing errors because of having the wrong principals as their owner.In this article,I will explain how to fix issues that are related to incorrect or wrong ownershipassigned to various securables.
Solution
Every securable in SQL Server has an owner. SQL Server assigns the owner tothe securable during creation to secure and ensure permanent access to thoseobjects by owners so the object can be managed. The idea behind assigningownership as part of SQL Server security is that the owner of any securable musthave unchangeable permissions and prevent any kind of lockdown situation in caseaccess is revoked for that securable. We cannot remove privileges from an objectowner and neither we can drop any principal (users or logins) if they ownobjects.
If you need to drop users or logins from SQL Server, then you need to changethe ownership first. Database and object ownership can be transferred using the ALTER AUTHORIZATIONcommand. You either need to be part ofthe sysadmin fixed server role orhave at least TAKE OWNERSHIP permission on the database and IMPERSONATE permissionon the new owner login to run ALTER AUTHORIZATION.
Note: Ownership of some entities in SQL Server cannot be changedlike linked servers, statistics, constraints, rules, defaults, triggers, ServiceBroker queues, credentials, partition functions, partition schemes, database masterkeys, service master key and event notifications.
When ownership is transferred, permissions on schema-contained objects that donot have explicit owners will be dropped. We cannot change the owner of sys, dbo,or information_schema.
Below, I will show how to assign or transfer ownership of various entities inSQL Server.
Change Database Ownership in SQL Server
Databases are owned by the login used when the database was created. We can changedatabase ownership to any server level principal or login after database creation.
Let’s understand using an example. You have created a database using yourlogin and did not change the owner to some login like sa. Later if youleave the organization and somebody from the DBA team tries to drop your loginto clean up the system, SQL Server will not allow the login to be dropped becausethe login owns a database. If you try this, you will get the below error.
Login 'DomainName\abc' owns one or more database(s).
Change the owner of database(s) before dropping thelogin. (Microsoft SQL Server, Error:15174)
The solution to fix this issue is to change the ownership of the database tosome other login and then drop the login you no longer need. Let’sdemonstrate it in step-by-step process on how to change the database ownership.
The first step is to check database ownership of identified database by runningthe belowcommand.
--Check the ownership of database "AdventureWorks2019"SELECT name as [DB Name], suser_sname(owner_sid) as [Owner] FROM sys.databasesWHERE name='AdventureWorks2019'
You can see the database is owned by a local administrator account.
Now, let’s change the ownership to the sa account by running the below ALTER AUTHORIZATION statement.
--Change the ownership of database "AdventureWorks2019" to saALTER AUTHORIZATION ON DATABASE::AdventureWorks2019 TO sa;
Have a look at below screenshot of above command.
Now you can run the first command again to verify theownership and you can see the ownership now shows as sa. Now you if you wantedyou could drop the login that was throwing error 15174.
Change Table Ownership in SQL Server
Let’s discuss how to change the ownership of tables. There are manyrequirements under which we need to change the ownership of database objects liketables, views or procedures. ALTER AUTHORIZATION can be used to change theownership of all such database objects.
First run the below T-SQL statements to get the table owner for table "Address"in the AdventureWorks2019 database.
--Check the ownership of table "Address"SELECT name as [Table Name], USER_NAME(principal_id) AS [Table_Owner], type_descFROM sys.objectsWHERE name = 'Address' AND type='U'
You can see the table owner is NULL in the below screenshot.
Now let’s go ahead and assign an owner to this table. Run the belowALTER AUTHORIZATION statement to change the table owner to user "test". I hadcreated the test login and user just for demonstration purpose, so you can useany login and user in your database.
--Change the ownership of table "Address"USE [AdventureWorks2019]GOALTER AUTHORIZATION ON OBJECT::[Person].[Address] TO test; GO
You can see the command completed successfully in the below image.
Below we can see the ownership has changed.
If any of your tables have indexed views, then you cannot change ownership andyou will get the below error if you try to change ownership. You can see I am changingthe table owner of table "Product" to user test in the below screenshot, but itgives me an error that the table has an indexed view so we cannot changethe owner of the table. We would need to drop the indexed view first, makethe change and then recreate the indexed view.
Change Schema Ownership in SQL Server
This section will educate you on two points. One is about how to check a schemaowner and another will teach you how to change the existing owner of a schemato another owner.
Run the below command to check the schema owner. You can see I am checking ownershipof schema "Person".
--Check the ownership of schema "Person"USE AdventureWorks2019GOSELECT schema_name, schema_ownerFROM information_schema.schemataWHERE schema_name = 'Person'
The owner of schema "Person" is showing as test in the below screenshot.Now we need to change it to dbo or some other user which you want to assign.
Run the below ALTER AUTHORIZATION statement to change the owner of schema "Person"from test to dbo.
--Change the ownership of schema "Person"USE AdventureWorks2019GOAlter AUTHORIZATION ON SCHEMA::Person TO dbo; GO
The command executed successfully.
Now, run the above command again to see the schema owner change.
Change Endpoint Ownership in SQL Server
Endpoints are server level securables which needs a server level login as theirowner. You cannot assign a database level principal like users as the owner for an endpoint.If you are running Always On Availability Groups and your endpoint being used forAOAG has the wrong or incorrect owner, then your AOAG might have a problem. Here, I willshow you how to check the ownership of endpoints and how to change endpointowners.
Run the below command to get the owner of all the endpoints on your SQL Serverinstance.
--Check the ownership of Endpoint "TSQL Default TCP"USE masterGOselect name as [EndPoint Name], USER_NAME(principal_id) AS [EndPoint_Owner], protocol_desc, state_descfrom sys.endpoints GO
You can see one endpoint has a NULL value as its owner. We need to change it toa correct login.
I ran the below command to change the ownership of this endpoint "TSQL DefaultTCP". You can see I used ALTER AUTHORIZATION to change its ownershipto sa.
--Change the ownership of Endpoint "TSQL Default TCP"USE masterGOALTER AUTHORIZATION ON ENDPOINT::[TSQL Default TCP] TO sa; GO
The command executed successfully as shown below.
Now, we will again validate the ownership of all endpoints by running the same commandthat we ran in the first step of this section. You can now see the ownership of theendpoint shows as dbo.
Although this example is to set the correct ownership of an endpoint,whatever login is used for the endpoint owner cannot be dropped until theendpoint ownership is transferred to another login.This can be very useful in case you are stuck in such a scenario and you are strugglingto transfer ownership. If any login owns an endpoint and you are tryingto drop that login then you will get the below error:
Drop failed for Login "DomainName\abc"
The server principal owns one or more endpoint(s) and cannot be dropped. (Microsoft SQL Server, Error:15141)
Use ALTER AUTHORIZATION to change the endpoint ownership beforedropping the login.
Next Steps
- ALTER AUTHORIZATION can be used to change ownership in SQL Server,Azure SQL Databases, Azure SQL Data Warehouse and Parallel Data Warehouse forobjects and databases. You can now go ahead and use it whenever you need to changethe ownership of any entity in SQL Server. This command ALTER AUTHORIZATION is veryuseful in fixing various errors which we get due to ownership dependencies.
About the author
Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.
View all my tips