November 14, 2007 at 10:34 am
What would be the simplest way to construct a custom role that would allow read-only access to all tables within a given database, but still allow ability to use the CREATE\DROP TABLE and CREATE INDEX DDL statements against the TempDB? This role would also need ability to EXEC all SP's and UDF's.
As many shops have, in our shop there is a constant tug of war between how much access to give developers to a production database. At the moment because of the way many of the applications are designed (some of them being packaged 3rd party apps), the developers have full dbo rights to the databases behind these applications (yes I hear the gasps out there). Management is looking to enforce new developer access rules so that developers have only read access to the databases (now come the cheers) but this will not work effectively for some of the applications.
For better or worse, A few applications have lightweight UI's (meaning weak editing rules as well as little business logic) and all the business logic is located in large & complex stored procedures or triggers. 95% of the time any problems are data related because something slipped by the weak UI editing rules. Also 95% of the time the developers can currently troubleshoot these very quickly by cutting & pasting the core of the script in the affected SP's into a Query Analyzer script window to run it as a standalone query to inspect the result set output, and often also displaying the results in one or more intermediate temporary tables that are used heavily in the application's SP's. Besides the heavy use of temp tables many of the SP's call other SP's or UDF's so EXEC permissions apply here also.
If the developers are locked down with read-only access, they will not be able to perform this kind of analysis because of the CREATE\DROP TABLE DDL statements against the TempDB and the EXEC requirements. Also there is much use of clustered and\or covering indexes for the temp tables which are also DDL statements likely to be unavailable with read-only access.
Thanks in advance for any assistance provided on this question.
maddog
November 14, 2007 at 5:03 pm
In SQL Server 2000 there isn't a role that will give you EXECUTE rights against stored procedures and UDFs, but you can create one fairly easily. To address this problem I wrote an article about creating an executor role but the gist of it is simple:
- Create a database role
- Script EXECUTE rights for the role against all the stored procedures and UDFs
- Make developers of the role
As far as read permissions, there is a fixed database role known as db_datareader. It grants implicit read access to all tables and views, including system tables. That means they should be able to view the definition of any object by being a member of this role. In SQL Server 2005 things are a bit different, you can grant VIEW DEFINITION rights (as well as EXECUTE rights) against all objects in a database, but I'm guessing you are still on SQL Server 2000.
K. Brian Kelley
@kbriankelley
November 15, 2007 at 8:26 am
Thanks for the response Brian.
I believe I've seen samples (possibly yours) and can work out the scripted permissions for the EXECUTE part, but will your suggestion of using the db_datareader role and the scripted EXECUTE permissions also allow using the CREATE\DROP TABLE and the index creation DDL statements against the TempDB?
Regards,
maddog
November 15, 2007 at 1:49 pm
No. I hadn't had time to experiment to see what exactly is needed for this.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply