The Developer’s Platform

What’s the best permission scheme that permits developers to fabricate reports, but protects a clinical database?

Fine-tuning the permissions under SQL 2000 was difficult. As mentioned in a previous post, the Clarity module shipped prior to SQL 2005, when permissions became more granular. Also, Clarity was developed with two other vendors’ databases also in mind.

We needed a solution long before our upgrade to SQL 2008. In the following, I’ll describe the steps we took under SQL 2000 and those that we took to extend the capability under SQL 2008.

A new database on the same server, Reporting_SRVS, was created. Two groups were created in the Active Directory, RptDev and RptBrowse. The latter was used extensively in the permissions of the production Reporting Services server.

In the main vendor’s database, Clarity, RptBrowse and RptDev were made members of the db_datareader role. RptBrowse was made a member of the db_denywriter role.

In the Reporting_SRVS database, RptDev was made a member of the db_owner role.

The developers created all views in the Reporting_SRVS database, referring with a three part name to an object in the Clarity database, for example, a view Report_SRVS.dbo.TestView was created with the command Select fld1,fld2 FROM Clarity.dbo.PAT_ENC.

Once we were under SQL 2008, I searched repeatedly for references on this topic. It was only long afterwards that one pertinent reference was found: SQL Server Best Practices – Implementation of Database Object Schemas, at http://msdn.microsoft.com/en-us/library/dd283095.aspx . However, Epic had not created the Clarity database using multiple schemas and we did not wish to open that can of digital worms. Fortunately, SQL 2008 permitted a number of enhancements to be added. The following assignments were made after the upgrade:

For the MSDB database, RptDev made a role member of the relatively new DatabaseMailUserRole. This allowed the report developers to craft TSQL scripts to generate email. Furthermore, RptDev was made a member of the SQLAgentReaderRole, to allow the developers to monitor the SQL Jobs.

SHOWPLAN permissions were granted to RptDev on the Clarity database (this permission was implicitly assigned to Reporting_SRVS database due to the db_owner role permissions). The granting of SHOWPLAN does create a possible security hole. Please read the caveats on Books on Line.

One last problem remained. The developers gradually transited from placing SQL batches to stored procedures in their Reporting Services reports. They soon discovered that they could run a stored procedure in SQL Manager, but it would not run in a Reporting Services report. The developers were instructed to grant Execute permissions for each stored procedure specifically to the account in Reporting Services authorized to access the data. We decided not to implement an automated assignment (as outlined in http://www.sqlservercentral.com/articles/Security/sqlserversecuritythedb_executorrole/988/ ).


Now the developers have nearly complete rights to their own developed objects in their database, yet the data is secured in the vendor’s database. Only vendor objects (and a few custom indexes) are in the Clarity database. The developers can monitor SQL jobs pertinent to the databases (although they can’t modify any). They can also examine execution plans and become aware early in the development process of queries that may perform poorly. SQL Manager 2008 also suggests indexes that may be missing and the developers are instructed to keep a log of these for the DBA to review.

There is one downside to this approach. We’ve been informed that Indexed views are not supported spanning one database to another but I haven’t confirmed this by attempting to create one. The benefits far outweigh the problems, however and this has worked well for some time.