"Dweller" <Dwel***@discussions.microsoft.com> wrote in message
news:A8DC5918-7D26-425C-851B-F5EE9BF6A4F3@microsoft.com...
> Hi,
>
> Looks like "EXECUTE AS" is not always works as expected after reading
> help.
> Lets try next steps for demonstration:
>
> 1. Create SQL login ( lets say "MASTER" ) with "processadmin" Server Role
> and
> use it to connect to any database (with 'dbo').
>
> 2. Execute "SELECT * from master.dbo.sysprocesses" statement and mark the
> result (all records).
>
> 3. Crete next SP and execute it
>
> CREATE PROCEDURE Master.MxSys_SysProcesses
> WITH EXECUTE AS CALLER
> --WITH EXECUTE AS OWNER
> --WITH EXECUTE AS 'MASTER'
> AS
> BEGIN
>     SELECT * from master.dbo.sysprocesses
>     RETURN 0
> END
>
> Mark results (also all records).
>
> 4. Drop the SP and recreate it with  "WITH EXECUTE AS OWNER" or "WITH
> EXECUTE AS 'MASTER'" option.
> Execute it. See results. They are different from #2 and #3 (just one
> record!). But expected to be the same.
>
> When I saw it I said: "Oops!". What would you say? Bug or somethign
> missing
> in help?
> BHW, the same is true for sys.dm_exec_requests and sys.dm_exec_sessions
> views.
>
> Thanks.

> What version of the CTP?
September.



>
> Regards
> --------------------------------
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
>
> IM: m***@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "Dweller" <Dwel***@discussions.microsoft.com> wrote in message
> news:A8DC5918-7D26-425C-851B-F5EE9BF6A4F3@microsoft.com...
> > Hi,
> >
> > Looks like "EXECUTE AS" is not always works as expected after reading
> > help.
> > Lets try next steps for demonstration:
> >
> > 1. Create SQL login ( lets say "MASTER" ) with "processadmin" Server Role
> > and
> > use it to connect to any database (with 'dbo').
> >
> > 2. Execute "SELECT * from master.dbo.sysprocesses" statement and mark the
> > result (all records).
> >
> > 3. Crete next SP and execute it
> >
> > CREATE PROCEDURE Master.MxSys_SysProcesses
> > WITH EXECUTE AS CALLER
> > --WITH EXECUTE AS OWNER
> > --WITH EXECUTE AS 'MASTER'
> > AS
> > BEGIN
> >     SELECT * from master.dbo.sysprocesses
> >     RETURN 0
> > END
> >
> > Mark results (also all records).
> >
> > 4. Drop the SP and recreate it with  "WITH EXECUTE AS OWNER" or "WITH
> > EXECUTE AS 'MASTER'" option.
> > Execute it. See results. They are different from #2 and #3 (just one
> > record!). But expected to be the same.
> >
> > When I saw it I said: "Oops!". What would you say? Bug or somethign
> > missing
> > in help?
> > BHW, the same is true for sys.dm_exec_requests and sys.dm_exec_sessions
> > views.
> >
> > Thanks.
>
>
>