SDM - installation/update with limited 'sysadmin' for MS SQL

[J W]

Scenario:

Customer will have SDM installed on a shared MS SQL server.  During installation and certain patches/uprgades, the SQL 'sa' account is needed.  They need to have separation of duties between application and database roles and also need to audit access and usage of privileged accounts.  While having the DBA present to enter the credentials during the installer/patch would solve the first requirement; it is cumbersome and means scheduling their time. It is preferred that specific service accounts are used that are restricted to their respective applications' databases.  These are maintained in AD.

 

Question:

What is the best practice for setting up a new 'sysadmin' account that will have permission to both create the mdb and users initially and also be able to modify it later - without having access outside those functions?  Should the DBA setup a placeholder 'mdb' database in order to then grant the necessary permissions to the new 'sysadmin' account?  Will the installer recognize this or will it error because the database already exists?

 

I did a search for 'sysadmin' online and found this TEC617177.

How to use SetupMDB to upgrade the backend MDB database for SQL Server

Which sets out the permissions for the DBUSER:

////////////////////

DBUSER/ DBPASSWORD: This is the SQL user that will be used to run the install and its password. Generally speaking, the user should be sa, but in lieu of being given the sa credentials, one can run using any user, so long as it is a user defined in SQL Server, and:

 

•   The user's default schema is set to "dbo" on the mdb (user mapping)
  
•   Default database is set to "master" (General setting on the user properties)
  
•   Server Roles for sysadmin and public are turned on.
  
•   User Mapping for master, model, msdb, tempdb are all set to dbo schema
  
•   For the MDB database, the user should have db_owner and public privileges.

/////////////////////

 

Which is why I asked about setting up a placeholder 'mdb'.

 

How are other users doing this?

 

thanks,

J.W.

[Lindsay_Estabrooks]

Hi Jeff,

 

I have found it easiest with all my clients to ask the dba to set up an "sa" equivalent account (I usually suggest "ca") that will be used during the install/upgrade. The dba can simply disable the account after the install/upgrade has completed. This is not the account that will be used by the application.

 

Cheers,

Lindsay

[J W]

Thanks for the response, Lindsay!

 

I know that is the easiest way and most customers are alright with that.  I guess I'm trying to confirm that the explicit permissions laid out in the TEC doc are sufficient.  I plan to incorporate those into the best practice documents for when the separation of duties are more strict.

 

Chris, I'll marked this as answered and others can add as needed.

 

J.W.

[System]

Thanks JW!

J W wrote:

 

Thanks for the response, Lindsay!

 

I know that is the easiest way and most customers are alright with that.  I guess I'm trying to confirm that the explicit permissions laid out in the TEC doc are sufficient.  I plan to incorporate those into the best practice documents for when the separation of duties are more strict.

 

Chris, I'll marked this as answered and others can add as needed.

 

J.W.

[J W]

Follow up:

 

Can we create a response file to set the DATA and LOG directories for the mdb during creation or does this always use the SQL server default?  I don't see a specific parameter set or call out in the setupmdb.bat or the setupmdb_pre.bat files.

 

thanks,

 

J.W.

[J W]

I followed up with Support and there does not appear to be a way to pass a response file or parameter list to the installer at this point.

 

J.W.