Re: EXECUTE AS question

stavros (stavros@xxxxxxxxxxxxxx) writes:
The procedure creates successfully, and it does execute under a
different security context - I can see this because the login column =
sa. However, it only returns one record, for the current session only
(as it would for a user without the VIEW SERVER STATE permission). Of
course, if I connect as sa and run sp_who2, I see information from all
sessions.

To be clear, if I log in as sa, sp_who2 returns all sessions (as
expected). The SP
above does in fact execute as sa (as expected). However, it
still only lists the current session, as if it's executing without the
VIEW SERVER STATE perm. That's the piece I don't understand, and what
I need a solution for.

This because when you impersonate a user, you are sandboxed into the
current database, unless that database is marked as trustworthy.

An alternative solution is to sign the procedure with a certificate,
and then associate create a login for the certificate, and then grant
that login VIEW SERVER STATE.

I have an article on my web site that covers this in detail:
http://www.sommarskog.se/grantperm.html



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: EXECUTE AS question
    ... However, it only returns one record, for the current session only ... (as it would for a user without the VIEW SERVER STATE permission). ... An alternative solution is to sign the procedure with a certificate, ... and then associate create a login for the certificate, ...
    (microsoft.public.sqlserver.security)
  • Re: user should not logged in more than one using diffferent machins o
    ... Added to that, you can always check the current session id is present in the database against the user who is trying to login, if it is not there you can allow him to logon otherwise, the user is logged in regardless of same machine or different machine. ... I am using sql server 2000 to store user database. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Please! Doesnt anyone know a better way to do this?
    ... account, they need to automatically be directed to the page to enter data ... session variable on the Account page. ... I assume here that you're checking a database when the user attempts to ... When a new user attempts to login or clicks to register, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: nooB PhP login using MySQL
    ... This PC has MySQL and IIS configured and running. ... how do I prevent users from bypassing the login? ... create a random string and store in session on server, ... euphemism for md5) but now that there are secure ways, ...
    (comp.lang.php)
  • Re: nooB PhP login using MySQL
    ... This PC has MySQL and IIS configured and running. ... how do I prevent users from bypassing the login? ... create a random string and store in session on server, ... euphemism for md5) but now that there are secure ways, ...
    (comp.lang.php)