sp_setapprole problem (using LINQ)

  • Thread starter Thread starter curiosa
  • Start date Start date
C

curiosa

We have an application that need to call set application role to give
rights to the database objects.

I call 'sp_setapprole' but it's running into error when I try to
GetUser to checks if the change was successfully.

Code:
ExecuteCommand("sp_setapprole 'rolename', 'password'");
'Select User_Name()'

It returns the message: "SQL Error - A severe error ocurred on the
current command. The results, if any, should be discarded" (twice)

In Event Log:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 18059
....
....

Description:
The connection has been dropped because the principal that opened it
subsequently assumed a new security context, and then tried to reset
the connection under its impersonated security context. This scenario
is not supported. See "Impersonation Overview" in Books Online.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.


When I use connection string with Polling=false, it runs without
error. But the set application role is ignored.


Do you know what is the problem with executing the approle ?
How can I work with setting application roles ?

Thanks in advance
 
The short answer is that when you run sp_setapprole you're switching
the security context inside of the database, which persists for the
duration of the connection. However, when the connection is returned
to the pool it is not reusable. This is described more clearly in
Erland Sommerskog's paper
http://www.sommarskog.se/grantperm.html#approles. The following KB
article has detailed technical information about the pooling aspect
http://support.microsoft.com/kb/229564.

--Mary
 
Back
Top