SQL Server Security – How to Create Databases, Logins, and Users with Minimal Privileges

installationSecuritysql server

I have an application installation package that needs to

  1. Create 1 or more databases (current process is to restore the database from a .BAK file made from a template that is adjusted periodically)
  2. Create SQL Server logins for the server (two SQL logins a MyAdmin and MyUser account)
  3. 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]

generates

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.

Best Answer

If the DBA doesn't want to grant sysadmin or securityadmin role memberships (needed to create logins), have the DBA create those logins prior to installation and add MyAdmin to the dbcreator fixed server role. The dbcreator role membership will provide the permissions to create, alter, and drop any database. No server-level permissions need be granted to MyUser before installation since the login will have CONNECT by default.

The MyAdmin account will own any databases created or restored under that security context. As the database owner, the account will be mapped to the dbo user and have full database permissions for administrative functions, including creating users and modifying schema. This account should be used only by the installer. Routine database access should be done as MyUser with only the needed permissions granted.

--A sysadmin or securityadmin role member creates logins
--before instalation and adds MyAdmin login to dbcreator role.
CREATE LOGIN MyAdmin WITH PASSWORD='$&Sl~dg$30=asz';
CREATE LOGIN MyUser WITH PASSWORD='MM20&%93.f04}y';
ALTER SERVER ROLE dbcreator
    ADD MEMBER MyAdmin;
GO

--Installer connects with MyAdmin login.
--Database will be owned by login running RESTORE (MyAdmin)
RESTORE DATABASE ApplicationDatabase
FROM DISK='D:\SqlBackups\ApplicationDatabase.bak'
WITH 
      MOVE 'ApplicationDatabase' TO 'D:\SqlDataFiles\ApplicationDatabase.mdf'
    , MOVE 'ApplicationDatabase_log' TO 'L:\SqlLogFiles\ApplicationDatabase.ldf'
    , STATS=5, REPLACE;
GO
--Database owner will access resotred database as dbo user.
USE ApplicationDatabase;
GO
--dbo user can create user mapped to SQL login MyUser.
CREATE USER MyUser; 
GO
Related Question