Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can i set ISOLATION LEVEL at user level ? Expand / Collapse
Author
Message
Posted Saturday, September 22, 2012 11:54 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:06 AM
Points: 87, Visits: 320
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.
Post #1363193
Posted Sunday, September 23, 2012 12:09 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
One way would be to change the default option in his or her SQL Server Management Studio (Tools menu, Options...):





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi


  Post Attachments 
Forum.png (99 views, 67.65 KB)
Post #1363195
Posted Sunday, September 23, 2012 1:03 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:06 AM
Points: 87, Visits: 320
Thanks SQL Kiwi

is there a way that i can enfore it for that user from my side as a dba.
Post #1363198
Posted Sunday, September 23, 2012 2:10 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 4:51 AM
Points: 9,928, Visits: 11,199
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363202
Posted Sunday, September 23, 2012 10:02 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, November 20, 2014 1:06 AM
Points: 87, Visits: 320
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.
Post #1363241
Posted Sunday, September 23, 2012 11:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2012 6:20 AM
Points: 1, Visits: 5
Good information. Thanks.
Post #1363330
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse