Consider the following scenario: You created a Window or SQL login in SQL Server and in the user mapping along with assigning a database role membership, you assigned as default schema something different than the “dbo”.
This article, suggests a way of transferring ownership of all database objects back to DBO in case is required.
Example of User Database Permissions in SQL Server
Take as an example the following screenshot:
As you can see, the SQL login “testLogin” has been assigned the database role “db_owner” on the database “SampleDB1” but along with that, “testLogin” was also set as the default schema.
If you do that, any object the login “testLogin” creates, their owner schema will be “testLogin” and not “dbo”.
If however, for any reason you want to remove “testLogin” from the SQL Server instance you will not be able to do it because the login, through its schema, will own database objects.
If you still want to remove the login don’t lose hope, there is a way! 🙂
How to Transfer Ownership of all Database Objects Back to DBO
For achieving the above you need to transfer back the ownership of the objects to the “dbo” (or any other) schema.
You can do that using the “ALTER SCHEMA” command. As described in BOL, this command transfers a securable between schemas.
So how can you use this command in order to transfer back to dbo all objects owned by “testLogin”?
One of the easiest approaches is to generate the set of SQL statements that you can run in order to restore ownership for all objects of the following types:
– functions (scalar, table-valued)
– stored procedures
– …and maybe any other object types
Suggested T-SQL Script
--User Option 1: Set the database (in this example "SampleDB1") USE SampleDB1 GO --Variable declarations DECLARE @currentSchema VARCHAR(100) DECLARE @newSchema VARCHAR(100) --User Options 2 and 3: set the current schema and the new schema --to which you want to transfer the selected database's objects --in this example current schema='testLogin' and new schema='dbo' SET @currentSchema='testLogin' SET @newSchema='dbo' DECLARE @schemaID INT SET @schemaID=(SELECT [schema_id] FROM sys.schemas WHERE [name]=@currentSchema) --The actual logic IF OBJECT_ID('TempDB..#Statements') IS NOT NULL DROP TABLE #Statements CREATE TABLE #Statements( ScriptToRun VARCHAR(200) ) --Generate statements for all user tables INSERT INTO #Statements SELECT 'ALTER SCHEMA ' + @newSchema + ' TRANSFER ' + @currentSchema + '.' + [name] FROM sys.objects WHERE type='U' AND [schema_id]=@schemaID --Generate statements for all user views INSERT INTO #Statements SELECT 'ALTER SCHEMA ' + @newSchema + ' TRANSFER ' + @currentSchema + '.' + [name] FROM sys.objects WHERE type='V' AND [schema_id]=@schemaID --Generate statements for all stored procedures INSERT INTO #Statements SELECT 'ALTER SCHEMA ' + @newSchema + ' TRANSFER ' + @currentSchema + '.' + [name] FROM sys.objects WHERE type='P' AND [schema_id]=@schemaID --Generate statements for all scalar functions INSERT INTO #Statements SELECT 'ALTER SCHEMA ' + @newSchema + ' TRANSFER ' + @currentSchema + '.' + [name] FROM sys.objects WHERE type='FN' AND [schema_id]=@schemaID --Generate statements for all table-valued-functions INSERT INTO #Statements SELECT 'ALTER SCHEMA ' + @newSchema + ' TRANSFER ' + @currentSchema + '.' + [name] FROM sys.objects WHERE type='TF' AND [schema_id]=@schemaID --Set "Results to Text" (Ctrl + T) SELECT * FROM #Statements
Note 1: As all our articles on our website, this article is provided as-is and for educational purposes only. Do not play with your Production environments if you do not know what you are doing and of course, always take backups of your data.
Note 2: In the above logic we searched for user tables and views, stored procedures and scalar/table-valued functions. You can search for even more database object types in a similar way (more info in one of my previous articles).
- How to Patch a SQL Server Failover Cluster
- How to Patch a Standalone SQL Server Instance
- How to Add a Database to a SQL Server Availability Group Using T-SQL
- Top 10 SQL Server DBA Daily Tasks List
- The “Public” Database Role in SQL Server
- Encrypting a SQL Server Database Backup
- Learn Azure Data Lake Analytics by Example
- Azure Cosmos DB: Learn by Example
- How to Create an Azure SQL Server Virtual Machine
- How to Backup a SQL Server Database from On-Premises to Azure Storage
- … all our SQL Server Administration Articles
Featured Database Security and Administration Tool: DBA Security Advisor
DBA Security Advisor: Secure your SQL Server instances by scanning multiple instances against a rich set of security checks, and by getting recommendations and remediation steps.
Did you find this article useful and interesting? Feel free to leave your comment!
Subscribe to our newsletter and stay up to date with our latest articles on SQL Server and related technologies!
Check out our latest software releases! All our software tools have 30-day Trial Versions which are free to download.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)