Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2012 Forums  SQL Server Administration (2012)  How to automate Windows SQL User and Permissions

Author  Topic  credgy Starting Member 1 Post Posted - 2014-11-05 : 19:27:27 SO I am trying to create 3 users with specific permissions for SCOM to monitor SQL databases. That means evert Instance on every SQL server in out organisation. I am not a DBA and need a simple way to replicate this process. I have all the permissions detailed I just dont know SQL well enough to know how to script this. I am sure there must be a relatively straight forward query I can run to do this. In fact I know a DBA who did this a year or so ago but he will nto share it for some reason? The permissions below are taken from the SCOM SQL MP Guide can anyone help please? 5 SQL PERMISSIONS FOR LOW PRIVILEGE MONITORING OF SQL SQL Discovery Account domain\svc_scomSQLDiscover • Member of domain group GROUP_SCOMSQLLP • This profile is associated with all discoveries. • Granted Log On Locally rights • Configure the “Log On Locally” local security policy setting to allow SQL Server logon SQL Monitoring Account domain\svc_scomSQLMonitor • Member of domain group GROUP _SCOMSQLLP • This profile is associated with all monitors and tasks. • Member of “Performance Monitor Users” local group on all SQL Servers • Member of “EventLogReaders” local group on SQL Servers running Windows 2008 and 2008 R2 otherwise Read Access is required on Application and System Event Logs • Granted Log On Locally rights • Configure the “Log On Locally” local security policy setting to allow SQL Server logon SQL Action Account domain\svc_scomSQLDefaultA • member of local Users group on SQL Servers • Configure the “Log On Locally” local security policy setting to allow SQL Server logon • Member of “Performance Monitor Users” local group on all SQL Servers • Member of “EventLogReaders” local group on SQL Servers running Windows 2008 and 2008 R2 otherwise Read Access is required on Application and System Event Logs • In SQL Server Management Studio, create a login for svc_scomSQLDefaultA on all SQL Server instances to be monitored on the agent machine, and grant the following permissions to each svc_scomSQLDefaultA login: VIEW ANY DEFINITION VIEW SERVER STATE • Create a svc_scomSQLDefaultA user that maps to the svc_scomSQLDefaultA login in each existing user database, master, msdb, and model. By putting the user in to the model database, you automatically create a svc_scomSQLDefaultA user in each future user-created database. See code sample below. You need to manually provision the user for attached and restored databases. • Add a svc_scomSQLDefaultA user on msdb to the “SQLAgentReaderRole” database role. SQL Monitoring Group GROUP _SCOMSQLLP • Domain Group • member “Performance Monitor Users” local group on SQL Servers • member of local Users group on SQL Servers • Configure the “Log On Locally” local security policy setting to allow SQL Server logon • Configure SQL Login in SQL Management Studio on all SQL Server instances to be monitored on the agent machine, and grant the following permissions to each “SQLMPLowPriv” login: -VIEW ANY DEFINITION -VIEW SERVER STATE • Create a “GROUP _SCOMSQLLP” user that maps to the “GROUP _SCOMSQLLP” login in each existing user database, master, msdb, and model. By putting user in the model database, it will automatically create a GROUP _SCOMSQLLP” user in each future user-created database. See the code sample below. You will need to manually provision the user for attached and restored databases. • Add the GROUP _SCOMSQLLP user on msdb to the “SQLAgentReaderRole” database role. • Member of “Performance Monitor Users” local group on all SQL Servers 5.1 GROUP _SCOMSQLLP Application Database/role Role/account All SQL Servers User Databases a. VIEW ANY DEFINITION b. VIEW SERVER STATE All SQL Servers Master a. VIEW ANY DEFINITION b. VIEW SERVER STATE All SQL Servers MSDB SQLAgentReaderRole Microsoft SQL Server 2012 OperationsManager dbmodule_users Microsoft SQL Server 2012 OperationsManager Grant CONNECT All SQL Servers Model a. VIEW ANY DEFINITION b. VIEW SERVER STATE SQL Low Privilege Group account permissions 5.2 svc_scomSQLDefaultA Application Database/role Role/account All SQL Servers User Databases a. VIEW ANY DEFINITION b. VIEW SERVER STATE All SQL Servers Master a. VIEW ANY DEFINITION b. VIEW SERVER STATE All SQL Servers MSDB SQLAgentReaderRole Microsoft SQL Server 2012 OperationsManager dbmodule_users Microsoft SQL Server 2012 OperationsManager Grant CONNECT All SQL Servers Model a. VIEW ANY DEFINITION b. VIEW SERVER STATE SQL Default Action account permissions To Grant Access to Start or Stop Service further permissions are required - This will need to be conferred with roles and responsibilities i.e Does a SCOM Admin have rights to restart a SQL Service or should that be a SQL DBA? In SQL Server Management Studio,add “svc_scomSQLDefaultA” to db_owner database role for each database to check: a. “Check Catalog (DBCC)” b. “Check Database (DBCC)” c. “Check Disk (DBCC)” (invokes DBCC CHECKALLOC) Grant the ALTER privilege to svc_scomSQLDefaultA” for each database on which to set state: a. “Set Database Offline” b. “Set Database Emergency State” Grant the ALTER ANY DATABASE privilege to svc_scomSQLDefaultA” login to run the task of “Set Database Online”.