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];
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:
will return 1(true) if the logged in user is member of the db_owner role (database scope!).
You may also use
— 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.)