(English) on SQL Server security: database owner

Leider ist der Eintrag nur auf Amerikanisches Englisch verfügbar. Der Inhalt wird unten in einer verfügbaren Sprache angezeigt. Klicken Sie auf den Link, um die aktuelle Sprache zu ändern.

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‘)