Sql-server – way to de-orphan users without setting context to a restored database

Securitysql server

I have an installation script that restores a database from a template.bak file and then makes a few alterations. This question flows on from another question: How do I create databases / logins / users with minimal privileges when installing an application?.

Prior to running the installation, a script is executed that creates two SQL Login accounts MyAdmin and MyUser with what I believe is the necessary privileges (MyAdmin is used by the installer and a server management application, MyUser is used by the client application):

create login MyAdmin with password = 'TemporaryPassword'
exec sp_addsrvrolemember 'MyAdmin', 'diskadmin'
grant create any database to MyAdmin
grant alter any database to MyAdmin
create login MyUser with password = 'TemporaryPassword'

In the installation script I connect with the MyAdmin account and restore the template.bak file. I can see that the database is created and MyAdmin is the owner of the database. However:
– the instruction use MyNewDatabase fails with The server principal "MyAdmin" is not able to access the database "MyNewDatabase" under the current security context.
– if I login as sa in my test environment, I can see that the dbo user in the database has a user type 'SQL user without login'.

This leaves me very surprised. How is that the MyAdmin user can create a database, but cannot use it? Why is the dbo user not assigned to the owner / creator of the database?

The advice I have read so far is to use alter user MyAdmin ..., however I need to switch context to the database use MyNewDatabase to accomplish this – which I cannot do while the login is not linked to the user – a very frustrating catch-22.

I have found that I can resolve the issue by performing all of these tasks with the SA account, however I will not have access to the SA account with the installer as this is a commercial application.

Is there a way to de-orphan the accounts without setting context to MyNewDatabase?

Best Answer

It seems the backup of the template database was taken with a user named MyAdmin in the database, resulting in the orphaned user after the restore. As the database owner following the restore, MyAdmin should not have a user account in the database because MyAdmin is implicitly mapped to the dbo user in the restored database.

I suggest you drop the MyAdmin user from the template database and take another backup. You will then be able to use the restored database as the MyAdmin login with the dbo user context. Under that context, you can execute ALTER USER MyUser; to fix the mapping for the non-dbo user.

Related Question