Re: EXECUTE AS question

On Mar 12, 4:16 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
stavros (stav...@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 factexecute assa (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, esq...@xxxxxxxxxxxxx

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

Thanks, Erland! I had read your excellent article, but apparently not
carefully enough. I was able to follow your advice of signing the
procedure with a certificate, and it works beautifully.

Stavros
.

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). ... and then associate create a login for the certificate, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • 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)
  • Re: why a session-based program behaves different on different computers
    ... But if you lose the session you lose ... now, pray-tell, HOW IS THE DATA LOST? ... IF THERE IS NO LOGIN, HOW DO YOU DETERMINE WHICH ROW IT IS? ... let's stick to the architecture for a moment then. ...
    (comp.lang.php)
Loading