Sysprocesses - useage in SQL 2005

Question

Sysprocesses - useage in SQL 2005

DanielP

SSCrazy

Points: 2320
More actions
August 29, 2007 at 1:08 pm

#176362

I have stored procedure that selects from master.dbo.SysProcesses to determine if any other users are connected to the application. It works fine in SQL 2000 but in SQL 2005 it only returns the user executing the stored procedure as the result.
I know you can grant VIEW laceName w:st="on">SERVERlaceName> laceType w:st="on">STATElaceType> permission but we use a user database ROLE for all users of the application. This Grant can only be set to a user and not a ROLE.
Is there any suggestion on how to make this work properly on SQL 2005?

Thanks,

Daniel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You can create a stored procedure with "execute as 'dbo'" like:

CREATE PROCEDURE foo2
    WITH EXECUTE AS 'dbo'
AS 
    SELECT  *
    FROM    master.dbo.sysprocesses
GO

You can grant execute to these to a user bar (CREATE USER bar WITHOUT LOGIN) :

GRANT EXECUTE TO foobar

Note that if bar executes the query now it will return only the single process, but you can then set the trustworthiness property on:

ALTER DATABASE foo2 SET TRUSTWORTHY ON

After this all the items are returned from SysProcesses.

Also, SysProcesses is depricated. In the future you should have a look at: sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests. These you can access from within the same database, and you may not need to set the the trustworthiness on your database.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

DanielP SSCrazy Points: 2320 More actions · Answer 2

Thanks for your help. I did have to add one additional step.

1. ALTER AUTHORIZATION ON DATABASE::dbname TO sa;

The database owners must match between the Master database and the user database.

Thanks,

Daniel

DanielP SSCrazy Points: 2320 More actions · Answer 3

Also, you still would need to set up trustworthiness if you use sys.dm_exec_sessions instead of sysprocesses.

But will use this instead of sysprocesses.