468,242 Members | 1,585 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Security for server tables on SQL 2000.

I need a user to have access to track all processes logged into our
SQL Server. In other words, I need them to be able to query
master.dbo.sysprocesses. I've already written the query they'll use,
and it works when logged in as sa, but not when logged in as the user
who needs to be able to do this.

I know that on SQL Server 2005, the syntax to grant this permission
would be:

grant view server state to <username>

Unfortunately, I need to do this on an older server running SQL Server
2000, and that syntax doesn't work to grant the permission. Any idea
what I'd need to grant the user to make this work?

Thanks in advance,

--Richard

Nov 3 '08 #1
1 1251
Richard (bl*********@yahoo.com) writes:
I need a user to have access to track all processes logged into our
SQL Server. In other words, I need them to be able to query
master.dbo.sysprocesses. I've already written the query they'll use,
and it works when logged in as sa, but not when logged in as the user
who needs to be able to do this.
What is the query? What does not "not work" mean?

As far as I recall, the information in sysprocess is public to everyone on
SQL 2000, so this is not likely to be a permission issue, but something
else. Unless someone explicitly has fiddled with the permissions on
sysprocesses. In such case, you should discuss with the DBA before
you fiddle anything more. What does "sp_helprotect sysprocesses" report?


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 3 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Noloader | last post: by
5 posts views Thread by VictorG | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.