I have an application installation package that needs to
- Create 1 or more databases (current process is to restore the database from a .BAK file made from a template that is adjusted periodically)
- Create SQL Server logins for the server (two SQL logins a MyAdmin and MyUser account)
- Create SQL Server users for each database (one for each SQL login)
At the moment I do this within the installation package by asking for SA credentials. I am trying to reduce the privileges required to install the application, since I am (understandably) getting pushback from DBAs who don't want to provide SA credentials to my installation package.
I have tried, prior to installation, creating a SQL Login with:
exec sp_addsrvrolemember 'MyAdmin', 'diskadmin' -- FYI the MyAdmin account will run backup/restore/duplicate commands grant create any database to MyAdmin grant alter any database to MyAdmin
If I do this I can still successfully create the database by restoring from .BAK as MyAdmin, but when I try to use the database (so I can create users in [New Database]) I get an error.
use [New Database]
The server principal "MyAdmin" is not able to access the database "New Database" under the current security context
Something tells me that I am taking the wrong approach. Should I be creating the database with a script instead of restoring from a .BAK? Can I use an account with less privileges than SA to execute all of these things, and if so what privileges would this account require? Should I remove these things from the installer package and have a separate script that can be run under the SA or another account that has the required privileges?
What I am looking for is a process that will create what I need with minimal privileges. If possible, I would like to minimise the changes required to the process I am currently using.