[:en]on SQL Server security: database owner[:]

[:en]When restoring a database from a different stage or derver don’t forget to check the login for dbo database user!

If the following statement doesn’t return e.g. sa but a developer login

SELECT SUSER_NAME(principal_id) FROM sys.database_principals WHERE name = ‚dbo‘;

you can change the login for the dbo user using the following statement:

ALTER AUTHORIZATION ON DATABASE::[THE_DATABASE_HERE] TO [sa];

Background:

The dbo user is the owner of the dbo schema which often contains many or all database objects.
Furthermore by default the user is member of the db_owner role.
The owner of the database is granted in parallel to the normal permission system !

To check effective login permisssions there are multiple options:

SELECT IS_MEMBER(‚db_owner‘)

will return 1(true) if the logged in user is member of the db_owner role (database scope!).

You may also use

use DATABASE_NAME_HERE

— optionally EXECUTE AS LOGIN = ‚MYDOMAIN\some.user‘;

select * from fn_my_permissions(null, ’server‘)

select * from fn_my_permissions(null, ‚database‘)

select * from fn_my_permissions(’schema.SchemaObject‘, ‚OBJECT‘)

— optionally REVERT;

A person may be member in multiple AD groups and therefore might have permisisons resulting from multiple AD group logins:

EXEC xp_logininfo ‚domain\userId‘,‚all‘; — elevated permissions required

alternatively off course also these (good for permission checking in views, procedures etc.)

SELECT IS_MEMBER(‚Domain\userId‘)

SELECT IS_MEMBER(‚Domain\ADGroup‘)

SELECT IS_MEMBER(‚databaserole‘)

 [:]