Clever Geek Handbook

How to change authenticated MS SQLServer login without closing and reopening the connection?

I have a connection to Microsoft SQL Server and want to change the authenticated user. Is it possible to do this without closing or opening the connection?

The ideal is something like Oracle set role .

I would really like the solution to work for SQL Server 2000 as well.

0


source to share


3 answers


You might want to take a look at Application Roles (sp_setapprole), but you should be aware of the implications that once the context is changed (for example, the role is set) it cannot be returned by SQL Server 2000 (this has been possible since 2005). The upshot of this is that the connection is effectively useless if closed in your code, for example. it cannot be returned to the pool and reused, which leads to scalability issues.



Otherwise, it is not possible to change the security context once it is established.

+1


source


As far as I know, SQL Server is very different from an account passed an authenticated context. Take Enterprise Manager and other tools like you have to disconnect and reconnect to change users.

Plus, looking at the way pooling works, it indicates that the connection itself is cached for the user, so if you change some of the executable sides the path through it will cause serious security problems.



So the short answer is, no, this is not possible as far as I know.

+1


source


Depending on what you are doing, EXECUTE AS can help you here. This allows you to execute SQL in the context of another user, similar to RUN AS accessible from the Windows shell. Profiler and audit tracing in SQL Server allows you to see both the original user and the context in which the statement is being executed.

EXECUTE AS USER = 'newuser'; SELECT ... <- SQL from context newuser REVERT;

Note. This is not available in SQL Server 2000 and has been added due to queries like yours.

0


source






More articles:


All Articles