0

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?

  • 1
    It might be possible if I change the create login script to set a SID - the same SID as the one in my original template database. I will look into this tomorrow (I'm in Australia - so it's nearly 5pm here). – Jamie Alford Apr 10 '17 at 6:43
1

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.

| improve this answer | |
1

When creating the login principals, set the SID of the login to the original login.

Original server select name, sid from templatedb.sys.database_principals to get the SID

New server create login MyAdmin with password = 'DummyPassword951', sid = 0xBCB4D3AC87D6F449979D6810B8E9E250

Edit: FYI MyAdmin is the owner of the template database and is mapped to the dbo user. In the installed version of the database, I have mapped the MyAdmin login to the dbo user, using the dbo SID from the template DB.

| improve this answer | |
  • 1
    Note that MyAdmin is the database owner after the restore. Although the explicit SID will correctly map the MyAdmin to the MyAdmin user, the SID for the restored database dbo user will be wrong because a login can be mapped to only one database user. – Dan Guzman Apr 10 '17 at 11:52
  • I have heard that it is better to have the SA account be the owner of all databases, which is not something I can enforce in the template database. What I should do is: - in the template database make SA the owner, create a MyAdmin user and map the MyAdmin login to the MyAdmin user. - in the destination server create an ApplicationInstaller account and restore the database with that, ensuring that MyAdmin is mapped to the new MyAdmin user SID. This way, the DBA can make SA the owner of the database and it will still work. – Jamie Alford Apr 10 '17 at 22:57
  • Actually, making SA the owner of all databases is a common practice (and it is convenient from a restoration point of view), but this blog provides reasons why that would be a bad idea from a security perpsective: insidesql.org/blogs/andreaswolter/2014/06/… – Jamie Alford Apr 11 '17 at 0:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.