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.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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:
CREATE SERVER ROLE role_name [ AUTHORIZATION server_principal ]
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.
CREATE ROLE role_name [ AUTHORIZATION server_principal ]
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:
ALTER AUTHORIZATION ON ROLE::MyRole TO [Domain\User];
See the syntax for ALTER AUTHORIZATION at: http://msdn.microsoft.com/en-us/library/ms187359.aspx
You can see logins from the server_principals table, in addition there multiple stored procedures that come with MSSQL to show membership or user role information. The following two links explain both well:
SQL Logins - sys.server_principals http://msdn.microsoft.com/en-us/library/ms188786.aspx
Database-Level Roles http://msdn.microsoft.com/en-us/library/ms189121.aspx
The record ID of the above information is then tied into database objects to show ownership information.