Sql-server – Grant Permission to Execute Set Transaction Isolation Level

sql serversql-server-2005

I'm a part-time accidental SQL Server 2005 DBA whose full-time job is developing business intelligence solutions, but where the BI intersects with the database, it falls in my 'stack'.

We have a number of scheduled reports that failed when another developer needed to add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in several stored procedures running against an enterprise application database.

It turns out that the login used by the reporting tool didn't have the correct permissions. It could execute the stored procedures, but not with the SET TRANSACTION ISOLATION LEVEL added.

I was able to get around it temporarily by adding the db_owner role to the login properties for the database. It already had db_datareader, db_datawriter, and public. But it seems like I should be able to grant a permission that would alleviate the problem without giving the login the db_owner role.

I've read through all the information on GRANT in the SQL Server books online, but can't find what permission to grant.

Can anyone help me out? If you know of a resource, I'd love to know about it.

Thanks!

Best Answer

First a quick point .. It's pretty unusual someone "needs" to use read uncommitted unless the need is to randomly get bad data.

Lecture done, permissions are generally found under individual commands. In this case you are looking at SET TRANSACTION ISOLATION LEVEL.

Typically you would find a Security/Permissions section, although in this case there isn't one. I believe that is because this is a command that anyone can run (although some isolation levels have to be enabled). Another place you can look for permissions is under the properties of the database or server. You will see a Permissions tab that will have a list of available permissions. But again I didn't find anything related to the command.

I'm mostly stumped by the fact that you granted db_owner and they worked. My guess is that it isn't the SET ISOLATION LEVEL that is actually causing the issue. Can you please include the actual error you are getting?

Related Question