Two related questions: Using SQL Server 2012, how does one find out who/what owns a user, and how can you set the ownership for users, if at all?
To add a couple of questions: 'how to' for both of the above with logins.
loginssql serversql-server-2012
Two related questions: Using SQL Server 2012, how does one find out who/what owns a user, and how can you set the ownership for users, if at all?
To add a couple of questions: 'how to' for both of the above with logins.
Best Answer
The sys.server_principals owning_principal_id is NULL except for Server Roles. Fixed Server Roles are owned by 'sa' (the server principal_id = 1) and cannot have ownership changed. You can create a new Server Role and set an owning principal using:
The sys.database_principals Fixed Database Roles are owned by
dbo
(database principal_id = 1) and cannot have ownership changed. But other Database Roles can be created and assigned to an owning principal_id.Both the ALTER SERVER ROLE and the database level ALTER ROLE only change membership or the name of the role.
Authorizations can be changed (providing you have the needed rights) by assigning a new owning principal by using ALTER AUTHORIZATION. Example:
See the syntax for ALTER AUTHORIZATION at: http://msdn.microsoft.com/en-us/library/ms187359.aspx