Post reply

can i set ISOLATION LEVEL at user level ?

  • September 22, 2012 at 11:54 pm

    #264696

    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.

  • September 23, 2012 at 12:09 am

    #1541139

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

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 23, 2012 at 1:03 am

    #1541142

    Thanks SQL Kiwi

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

  • September 23, 2012 at 2:10 am

    #1541145

    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

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • September 23, 2012 at 10:02 am

    #1541181

    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.

  • September 23, 2012 at 11:09 pm

    #1541263

    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