Guest User

Untitled

a guest
Apr 25th, 2018
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.15 KB | None | 0 0
  1. USE [master]
  2.  
  3. GRANT CONTROL SERVER TO [user1]
  4.  
  5. SELECT ObjectName = s.name + N'.' + o.name
  6. , SchemaOwnerName = dps.name
  7. , ObjectOwnerName = dpo.name
  8. FROM sys.objects o
  9. INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  10. LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
  11. LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
  12. ORDER BY s.name, o.name;
  13.  
  14. --create a test user
  15. CREATE USER OwnerTest WITHOUT LOGIN;
  16.  
  17. --create a table, and change the ownership to OwnerTest
  18. CREATE TABLE dbo.Test (id int NOT NULL);
  19. ALTER AUTHORIZATION ON dbo.Test TO OwnerTest;
  20.  
  21. --check the list of owners
  22. SELECT ObjectName = s.name + N'.' + o.name
  23. , SchemaOwnerName = dps.name
  24. , ObjectOwnerName = dpo.name
  25. FROM sys.objects o
  26. INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
  27. LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
  28. LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
  29. WHERE (dps.name <> s.name OR dpo.name IS NOT NULL)
  30. ORDER BY s.name, o.name;
  31.  
  32. DROP USER OwnerTest;
  33.  
  34. DROP USER OwnerTest;
  35.  
  36. DROP TABLE dbo.Test;
  37. DROP USER OwnerTest;
Add Comment
Please, Sign In to add comment