CLR context does not revert

Question

CLR context does not revert

Stewart Brightman

SSC Enthusiast

Points: 113
More actions
August 20, 2008 at 6:28 pm

#189659

I have created a CLR stored procedure owned by dbo and use the EXECUTE AS OWNER option to change its execution context. The stored procedure is called by a .Net application logged in as e.g. User1. Even though I do not explcitly use a REVERT statement at the end of the stored procedure, on some SQL servers it executes and the context reverts back to User1 after the stored proc has executed. On other servers however, the context of the calling connection remains set to 'dbo'. Is anyone aware of a server setting that might cause the different behaviour?

Viewing 3 posts - 1 through 2 (of 2 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Are you sure that the Owner (which may be different from the schema) is the same on all of them?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

First, make sure that a valid Login is the owner of the database. For instance, in my environment, I belong to a DBA Active Directory group which is a member of the sysadmin group on the SQL Servers. My personal account doesn't exist in the SQL Servers, but if I create a database, it will show me as the owner in SSMS, and there is no login for me on the server, so this creates problems. If this is the case, just change the owner with sp_changedbowner to a valid login and try again.