SQL Server separation of duties

By:   |   Comments (7)   |   Related: > Security


In many organizations there is a need to segment what DBAs can do in certain environments. By default most DBAs are made sysadmins, but this gives them full control over the SQL Server instance. To meet certain regulatory guidelines this is not allowed, because DBAs have access to sensitive data. By default there is not a way to segment out certain DBA tasks vs. viewing user data, so in this tip we look at a Separation of Duty Framework that was developed to allow you to achieve this.


Separation of duty, as a security principle, has as its primary objective the prevention of fraud and errors. In the face of internal and external regulatory requirements like PCI and SAS 70, organizations are realizing that the security and privileges of the DBA must be minimized for certain types of data that is categorized as sensitive.

SQL Server provides a rich set of security features that allow customers to satisfy their security goals. Everything from encrypting sensitive data to recording all activity (Audit) within a particular database is easily attainable with the features introduced in 2008. However, one topic that many struggle with is in the area of Separation of Duties or Role Separation. The subject frequently comes up when facing regulatory compliance, usually in the form of "How do I prevent my administrators from seeing my data?" While SQL Server does not provide a packaged solution to facilitate Separation of Duties, it does provide functionality that allows for Separation of Duties to be achieved.

The goal of this tip is to share with the community, Lara Rubbelke's idea on how we can achieve Separation of Duty within SQL Server.

At the 2010 PASS Summit, Lara Rubbelke (Data Platform Technology Specialist for Microsoft) presented a SQL Server Separation of Duty Framework that can be used to achieve Separation of Duty. The basic idea consists of delegating certain DBA tasks that require sysadmin rights to a Junior DBA or a non-DBA without granting him/her sysadmin rights.

The Separation of Duty framework can be downloaded for free from http://sqlserversod.codeplex.com/releases/view/55355, and it comes with the following pre-defined procedures:

separation of duty within sql server

For example; sp_who2 requires the VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server. If you do not have this permission, the user sees only their current session. However, using the Separation of Duty Framework a regular user is able to run a command similar to sp_who2 to see all sessions like a sysadmin, without having to grant the VIEW SERVER STATE permission directly. This methodology therefore allows organizations to create a set of standard procedures that allows a non-sysadmin to run, but still preventing the viewing of sensitive data.

Before installing the Separation of Duty Framework, please read the documentation that comes with it.

When installing, the installation script will ask for the following values:

ServerName The name of the instance where the SQL Server Separation of Duties Framework will be installed.
DatabaseName The name of the database which will be used to manage the signed procedures. This may be an existing database or a new database. It is highly recommended not to use an application database, and to use a database that is specifically designed for DBA management.
KeyBackupFileDestination The directory where the keys will be backed up during the installation.
MasterDMKPassword The Master database Database Master Key password. This will only be used if a Database Master Key does not yet exist in the Master database. If a Database Master Key exists in the Master Database, this password is disregarded.
PKBackupPassword The password used to encrypt the private key when backed up.
UserDMKPassword The password for the Database Master Key that will be created in the database specified in DatabaseName. If a Database Master Key exists in this database, the password is disregarded.

Using PowerShell, run ".\InstallScript" to run the installation.

using powershell to install the separation of duty framework

Once the installation is complete, the script will create the following procedures in the database that you specified in the installation process. In my case, I chose DBASoD as the database name.

the script will create the following procedures in the database

To test, I created a SQL user named DBA1 with the following permissions. (Note: DBA1 is not a sysadmin)

to test, create a sql server user that is not a sysadmin

Let's connect to SQL Server using the newly created user DB1 and run: sp_who2 (I only get my session information)

connect to sql server using the newly created user

Now, let's run: dba.sysproc_sp_who2 (I get back all information this account would see if it were a sysadmin)

this should get you closer to your goal of separating duties in your sql server enviroment

As you can see this is pretty straightforward to setup and hopefully it gets you closer to your goal of separating duties in your SQL Server environment.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, June 11, 2013 - 5:38:46 PM - rich Back To Top (25397)


Is there a method for implementing 'Separation of Duties', where specific columns (i.e. SSN) are 'masked' so that a sysadmin cannot read the actual value?

Saturday, March 16, 2013 - 9:29:44 AM - Matteo Back To Top (22829)

Sorry I have not tested it for SQL 2012.

Friday, March 15, 2013 - 11:53:14 AM - Patrick Back To Top (22818)



Does your Separation of Duty Framework works with SQL Server 2012 ?


Thanks for your response.


Best Regards



Monday, February 21, 2011 - 10:39:31 AM - Brian Davis Back To Top (12990)

Separation of Duties Framework V2 was released on 1/23/2011 and contains support for tiers within your security architecture.  The latest version can be found here http://sqlserversod.codeplex.com/.  Lara and I are continuing to work on this project and are working on the next release.

Muahmmad - The framework should work the same in SQL Server 2005.  Both documentation and samples are included in the download and should give you a starting point.

Sunday, February 20, 2011 - 5:43:33 PM - Lara Rubbelke Back To Top (12982)

Thursday, February 17, 2011 - 7:09:43 AM - Matteo Back To Top (12955)

Thanks for your comment. I have not tried it with SQL 2005 however, I think it works the same as in SQL 2008

Thursday, February 17, 2011 - 6:45:19 AM - Muahmmad Azim Back To Top (12954)

Hi Lorini

The topic is very interesting and worth implementable

Please give some guide lines for implementing it in SQL Server 2005 DBs.

get free sql tips
agree to terms