2

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.

1

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

0

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.

  • Thank you for the response. The sys.server_principals view and sys.database_principals view shows owning_principal_id. However, do you know how to change the owning principal id, if possible? – user3469285 Jun 24 '14 at 16:46
  • drop and recreate the item is the fastest, or you can ALTER SCHEMA – David George Jun 24 '14 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.