2

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!

Improve this question
2
  • 1
    Please merge your accounts Jeff and JeffRob.
    – Kin Shah
    Feb 3 '16 at 23:09
  • Jeff, reading your "answer" below, you can use Windows authentication to log in to SQL using SSMS if you know the account's password. You simply open a command prompt and run this command: runas /netonly /user:<domain>\<username> "<full path to ssms.exe>" .Replace <domain>\<username> with the account details and <full path to ssms.exe> with the full path to ssms.exe. You will be prompted to supply the account password and then ssms will use that account in any windows authentication attempts.
    – Mister Magoo
    Feb 3 '16 at 23:46
2

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.