Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Execute as issue

Author  Topic  Sully Starting Member 30 Posts Posted - 2009-01-06 : 16:14:35 Hi All, I have the following sp in the Master db ALTER procedure [dbo].[sp_RemoveUnusedLocks] with execute as 'dbo' as select spid from sys.sysprocesses --select * from sys.dm_exec_sessions I have a third party sp that used to work with 2000 but with a change in 2005 does not and I'm trying to work around it. It attempts to access the current active sessions from an account that can now only see it's own current session. The account is a SQL authenticated account and does not have VIEW SERVER STATE permission set. I moved the select into a sp in Master in order to use the "execute as" clause. And though there would be more to it, I'm currently just trying to get it to work as is. But no matter what user I try to "execute as" the select returns only the current session. This sp is called from another sp from a different db. One thing I don't understand is I can run the select of sysprocesses command from an EM query, using the same account as the "execute as" clause, and get all the current sessions. But if I execute the sp from that same query, I only get the current. Can someone clarify this for me, and/or point me in the right direction. Any help will be greatly appreciated. Stuck in neutral in a pedal to the metal world, Sully