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.

  • so the problem you are experiencing is that you cannot add users under MyAdmin login? – George K Apr 7 '17 at 6:42
up vote 1 down vote accepted

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

You should be able to do this without having to give any special permissions to your installation Login. Try the following:

  1. Create a stored procedure, in the [master] database, that encapsulates all of the installation steps (i.e. creating the new DB from a restore, creating the logins, and creating the users).
  2. Create an Asymmetric Key (or a Certificate would work just as well) in the [master] database (and my preference is to supply a password rather than rely on the Database Master Key):

    USE [master];
    
    CREATE ASYMMETRIC KEY [InstallationPermissionsKey]
      WITH ALGORITHM = RSA_2048
      ENCRYPTION BY PASSWORD = '.....';
    
  3. Create a Login from that Asymmetric Key (or Certificate):

    CREATE LOGIN [InstallationPermissions]
      FROM ASYMMETRIC KEY [InstallationPermissionsKey];
    
  4. Add the Key (or Certificate) -based Login to the sysadmin fixed server role:

    ALTER SERVER ROLE [sysadmin]
      ADD MEMBER [InstallationPermissions];
    

    This step defines the maximum set of permissions that can be used, but since Key and Certificate -based Logins cannot be impersonated, there is currently no mechanism allowing for these permissions to actually be used. Meaning: so far, no security risk at all. This Login is merely a container for permissions, similar to a Role, but it can be applied more selectively than a Role.

  5. Sign the installation stored procedure created in Step 1 above using ADD SIGNATURE:

    ADD SIGNATURE TO [dbo].[InstallPackage]
      BY ASYMMETRIC KEY [InstallationPermissionsKey]
      WITH PASSWORD = '{same password used in CREATE ASYMMETRIC KEY..}';
    

    This step associates the permissions granted to the Key or Certificate -based Login to the Stored Procedure. This is similar to adding a Login or User to a Role, but here we are granting the permissions to the code (i.e. the stored procedure) and not to a person. The difference is that code can only do what it is written to do, while a person can do anything allowed by the permissions that they are granted. Meaning, if the stored procedure is only coded to restore a backup, create those two Logins, and then create those two Users, then it doesn't matter that the Key-based Login was added to the sysadmin Server Role since anything else that that Role allows for will not be attempted. On the other hand, once you add a person to sysadmin, you can't really prevent them from doing anything (hence the concern of the DBAs). As long as the DBAs keep that password (used when creating the Asymmetric Key) a secret, then nobody else can sign any code with that Asymmetric Key as it requires the password. AND, if you update the stored procedure to do other things, then the signature is dropped and hence the permissions are lost. This forces the DBAs to review the change and either reject it or execute ADD SIGNATURE again to re-apply these elevated permissions.

  6. Grant your installation Login EXECUTE ON [master].[dbo].[InstallPackage] stored procedure.
  7. Remove your installation Login from the sysadmin fixed Server Role.

You are responsible for Step 1 (creating the installation Stored Procedure), and the DBAs are responsible for the rest.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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