How did removing an empty schema and changing the default schema remove db_owner role from a database user?
I determined to clean up the extra schemas that have been created over the years. Some schemas were created by default and have the same name as a SQL Database Login. I searched for schemas that did not contain any objects with this query:
SELECT
db_name() AS DatabaseName
,ds.SCHEMA_NAME AS SchemaName
,du.name AS UserName
,'USE ' + db_name() + '; ALTER USER [' + ds.SCHEMA_NAME + '] WITH DEFAULT_Schema=dbo; drop schema if exists [' + ds.SCHEMA_NAME + ']; ' as Fix
FROM INFORMATION_SCHEMA.SCHEMATA ds
JOIN sys.database_principals du ON du.name = ds.SCHEMA_NAME
AND du.default_schema_name = ds.SCHEMA_NAME
AND ds.SCHEMA_OWNER = du.name
WHERE NOT EXISTS (
SELECT 1
FROM sys.objects o
JOIN sys.schemas ss ON ss.schema_id = o.schema_id
WHERE ss.name = ds.SCHEMA_NAME
)
AND ds.SCHEMA_NAME NOT IN (
'dbo'
,'guest'
,'sys'
,'INFORMATION_SCHEMA'
)
Then used this query (as an example) to clean them up:
USE SecondaryDatabase;
ALTER USER [User2] WITH DEFAULT_Schema=dbo;
drop schema if exists [User2];
I first changed the default schema to the default "dbo". Then I removed the schema that had the same name as the user.
Removing the schema or changing the default schema removed the db_owner role from User2. The change our vendor made to fix it was to grant the db_owner role to User2.
I cannot understand how removing a schema that had no objects in it and changing the default schema could change this user's permissions. Any ideas?
What I have done and researched so far:
- https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/ownership-and-user-schema-separation?view=sql-server-ver16
- Basics about users and schemas.
- What is the purpose of "Schema Owner"?
- What I understand from this is that being a schema owner only grants permissions to objects in that schema. If there were no objects in the schema, removing the schema should not have changed the users permissions.
- Restored a copy of the database to another server. Re-ran the "fix" query to see if it would remove the user from the db_owner role. It did not.
- Restored a second copy of the database on the same other server. Used Schema Compare in Visual Studio to compare the restored original to the restored copy after running the "fix". I didn't observe any unexpected changes. Compared the restored copies to the production database.
- Specific version of the SQL Server is 2019 15.0.4410.1.
