Role Based Security Overview

In order to provide a more secure environment and still allow non System Administrators to take advantage of SQL Sentry's many features, roles are placed on the SQL Sentry database during its installation or upgrade. Users can be placed in these roles allowing them access to the features they need while restricting access to other features that may be above and beyond their particular responsibility.

 

Setting Up Role Based Security

Role Based Security is primarily configured through T-SQL statements or by using SSMS to setup database roles in SQL Server. First make the person a User on the SQL Sentry Repository database and add them to the allow_all role. This provides full access to the SQL Sentry repository.

  

From here you can add the User to any of the custom deny_ roles to restrict that User's access to the different functions of SQL Sentry . The role names are self-explanatory. Typically there is a role to deny updating the specified information, and one to deny reading the information at all.

 

Roles

allow_all	Provides full access to SQL Sentry 's features. Place all non - sa Users in this role, then add to deny roles to restrict access.
deny_actions_read	Denies the ability to view all General, Failsafe, and Audit actions.
deny_actions_update	Denies the ability to make changes to any actions, but allows the viewing of those settings, making them "read-only".
deny_appsettings_update	Denies any changes made under the SQL Sentry Server > Settings node.
deny_contact_update	Denies the ability to update information for individual Users, but allows viewing the information, making it "read-only".
deny_contactgroup_update	Denies the ability to update Group information, making it "read-only".
deny_eventchain_read	Denies the ability to view Event Chain information.
deny_eventchain_update	Denies the ability to make changes to Event Chains.
deny_settings_connection_read	Denies the ability to view information under the Settings tab at the Connection level.
deny_settings_connection_update	Denies the ability to make changes under the Settings tab at the Connection level.
deny_settings_object_read	Denies the ability to view information under the Settings tab at the Object level.
deny_settings_object_update	Denies the ability to make changes under the Settings tab at the Object level.
deny_settings_source_read	Denies the ability to view Source information from the Settings tab.
deny_settings_source_update	Denies the ability to make changes to Source information from the Settings tab.
deny_site_update	Denies changes made to Site Configuration.
deny_watch_connection	Denies the ability to watch or stop watching a Connection.
deny_watch_object	Denies the ability to watch or stop watching an individual Object.

 

An Example

For example, you may have a junior DBA that needs to be able to use SQL Sentry's Calendar View to check for any failures or long running jobs overnight, but you don't want them to be able to make changes to any of SQL Sentry 's settings.

 

First, add their login as a User on the SQL Sentry Repository database. Next, place that User in the allow_all role. This will ensure the User has access to all the information they need while being explicitly denied any information specified in the additional roles in which you place them. Finally, for this example, you may want to add this User to all "deny_" roles except the ones ending in "_read". This will deny changes to any settings along with the ability to watch or stop watching a connection or object. It is also important to remember that logins using SQL Server Authentication must be specified in the SQL Sentry Console connection information as well. Go to the File > Connect to Installation menu, uncheck the box marked "Integrated Windows Authentication", and enter the User's login and password. You will then have to restart the SQL Sentry Console. These new settings will remain in effect on this SQL Sentry Console until explicitly changed again.

 

NOTE: The roles starting with "db_" are SQL Server default roles placed on every database. Use of these roles in the SQL Sentry database may cause unpredictable behavior.