Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [master]
- GRANT CONTROL SERVER TO [user1]
- SELECT ObjectName = s.name + N'.' + o.name
- , SchemaOwnerName = dps.name
- , ObjectOwnerName = dpo.name
- FROM sys.objects o
- INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
- LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
- LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
- ORDER BY s.name, o.name;
- --create a test user
- CREATE USER OwnerTest WITHOUT LOGIN;
- --create a table, and change the ownership to OwnerTest
- CREATE TABLE dbo.Test (id int NOT NULL);
- ALTER AUTHORIZATION ON dbo.Test TO OwnerTest;
- --check the list of owners
- SELECT ObjectName = s.name + N'.' + o.name
- , SchemaOwnerName = dps.name
- , ObjectOwnerName = dpo.name
- FROM sys.objects o
- INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
- LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
- LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
- WHERE (dps.name <> s.name OR dpo.name IS NOT NULL)
- ORDER BY s.name, o.name;
- DROP USER OwnerTest;
- DROP USER OwnerTest;
- DROP TABLE dbo.Test;
- DROP USER OwnerTest;
Add Comment
Please, Sign In to add comment