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?