Changing the owner of a schema seems like a trivial DBA task, right? But, are you aware that, when you change the owner of a schema, behind the scenes, SQL Server silently drops all the permissions associated with the objects contained within that schema?
A real life scenario:
You’ve decided that a particular user is no longer needed in your database. You want to drop the user, but you cannot, as the user owns a schema, with some objects under the schema. You simply change the owner of the schema to another user or dbo using ALTER AUTHORIZATION command, and then drop the user. You’ve achieved your goal of dropping the user, and in the process, changed the ownership of the schema to another user.
This is where things get tricky! Unbenown to you, SQL Server quietly drops all the permissions associated with the objects within your schema, as soon as the owner is changed.
Imagine you have an application user account that uses those objects – this would result in your application failing with all sorts of ‘permissions denied’ errors and cause an outage. You then have to work out what permissions were previously granted, and re-grant those permissions on those objects. This gets messy, if you don’t have a permissions script saved in your version control somewhere. In the worst case, you’d have to restore the database as a copy, from a last known good backup, identify and script out the permissions, and reapply them to your database.
How do you go about handling this scenario?
Its simple – you just need to script out the permissions on the objects under the schema, for which you are changing the owner. Reapply the permissions after changing the schema owner. If you haven’t got a saved/version controlle script for your permissions, you can work out the current permissions by looking at sys.database_permissions.
Is this a bug in SQL Server?
No! its not a bug. This is by design as per Microsoft documentation. Look up Microsoft SQL Server documentation for “ALTER AUTHORIZATION (Transact-SQL)” and you will see this scenario documented.
Demo time!
Here’s a quick script that demonstrates the problem. To follow the demo, read my comments before each command:
USE master
GO
--Create two logins for our demo purpose
--Logins only get created if they don't already exist
IF SUSER_ID('DemoAdmin') IS NULL CREATE LOGIN DemoAdmin WITH PASSWORD = 'ThisIsADemoAdmin123!!!'
IF SUSER_ID('DemoUser') IS NULL CREATE LOGIN DemoUser WITH PASSWORD = 'ThisIsADemoUser123!!!'
GO
--Create a new database named DemoDB, if it doesn't already exist
IF DB_ID('DemoDB') IS NULL
BEGIN
CREATE DATABASE DemoDB
END
GO
--Change context to our new DemoDB
USE DemoDB
GO
--Create two new users in DemoDB, mapped to the two new logins created above
IF USER_ID('DemoAdmin') IS NULL CREATE USER DemoAdmin FOR LOGIN DemoAdmin WITH DEFAULT_SCHEMA = DemoAdminSchema
IF USER_ID('DemoUser') IS NULL CREATE USER DemoUser FOR LOGIN DemoUser
GO
--Create a schema named DemoAdminSchema owned by the user DemoAdmin
IF SCHEMA_ID('DemoAdminSchema') IS NULL EXEC ('CREATE SCHEMA DemoAdminSchema AUTHORIZATION DemoAdmin')
GO
--Output shows that the schema DemoAdminSchema is owned by the user DemoAdmin
SELECT name AS SchemaName, USER_NAME(principal_id) AS SchemaOwner FROM sys.schemas WHERE name = 'DemoAdminSchema'
GO
--Create a table and a stored procedure for the purpose of our demo
IF OBJECT_ID('DemoAdminSchema.Table1', 'U') IS NULL CREATE TABLE DemoAdminSchema.Table1 (i int PRIMARY KEY)
IF OBJECT_ID('DemoAdminSchema.Proc1', 'P') IS NULL EXEC('CREATE PROC DemoAdminSchema.Proc1 AS BEGIN SELECT i FROM DemoAdminSchema.Table1 END')
GO
--Granting permissions on the above table and stored procedure to DemoUser
DENY DELETE ON DemoAdminSchema.Table1 TO DemoUser
GRANT SELECT, INSERT, UPDATE ON DemoAdminSchema.Table1 TO DemoUser
GRANT EXEC ON DemoAdminSchema.Proc1 TO DemoUser
--Display the permissions
--Yes, the permissions exist
SELECT
OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) AS ObjectName,
USER_NAME(grantee_principal_id) AS GrantedTo,
USER_NAME(grantor_principal_id) AS GrantedBy,
state_desc AS PermissionState,
permission_name AS Permission
FROM
sys.database_permissions
WHERE
grantee_principal_id = USER_ID('DemoUser')
AND (class <> 0)
GO
--Change the owner of the schema DemoAdminSchema from DemoAdmin to dbo
ALTER AUTHORIZATION ON SCHEMA::DemoAdminSchema TO dbo
GO
--Now rerun the permissions query to see if the previous permissions still exist:
--Wow!!! THE PERMISSIONS ARE GONE
SELECT
OBJECT_SCHEMA_NAME(major_id) + '.' + OBJECT_NAME(major_id) AS ObjectName,
USER_NAME(grantee_principal_id) AS GrantedTo,
USER_NAME(grantor_principal_id) AS GrantedBy,
state_desc AS PermissionState,
permission_name AS Permission
FROM
sys.database_permissions
WHERE
grantee_principal_id = USER_ID('DemoUser')
AND (class <> 0)
GO
/*
To remove the logins and database that have been created as part of the demo,
run the below commands:
USE master
GO
DROP LOGIN DemoAdmin
GO
DROP LOGIN DemoUser
GO
ALTER DATABASE DemoDB SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE DemoDB SET ONLINE WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE DemoDB
GO
*/