can i set ISOLATION LEVEL at user level ?

  • Hi All,

    is it possible to set a default isolation level for a user?

    because, when i am giving permission for a developer, i don't want him to make shared-Locks through SELECTs with the busiest tables. so, i thought it would be better if his connection can be assumed as 'read uncommitted' isolation level.

    Appreciating your help.

  • One way would be to change the default option in his or her SQL Server Management Studio (Tools menu, Options...):

  • Thanks SQL Kiwi

    is there a way that i can enfore it for that user from my side as a dba.

  • SQL Mad Rafi (9/23/2012)


    is there a way that i can enfore it for that user from my side as a dba.

    Not that I know of. There might be some way to enforce or distribute the setting using Windows policies - but that's not something I know much about.

    Another solution you might consider is changing the database to use the read committed snapshot isolation level (ALTER DATABASE db-name SET READ_COMMITTED_SNAPSHOT ON). That way, readers at the default read committed isolation level will not take the shared locks that appear to be causing you a problem. There may be some impact on tempdb from enabling that option, more details in Books Online including the link below:

    http://msdn.microsoft.com/en-us/library/ms189050.aspx

  • SQL Kiwi (9/23/2012)


    SQL Mad Rafi (9/23/2012)


    is there a way that i can enfore it for that user from my side as a dba.

    Not that I know of. There might be some way to enforce or distribute the setting using Windows policies - but that's not something I know much about.

    Another solution you might consider is changing the database to use the read committed snapshot isolation level (ALTER DATABASE db-name SET READ_COMMITTED_SNAPSHOT ON). That way, readers at the default read committed isolation level will not take the shared locks that appear to be causing you a problem. There may be some impact on tempdb from enabling that option, more details in Books Online including the link below:

    http://msdn.microsoft.com/en-us/library/ms189050.aspx

    Thanks a lot Paul White.

  • Good information. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply