April 30, 2008 at 7:56 am
hi guys I am really in a dilemma here, is there any way to see the blocks happening in sql server without being a sysadmin?
the way i check for blocks is running this query but i don't have permissions in my production server.
SELECT
s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50
April 30, 2008 at 9:15 am
I don't think that you can get this to run without some rights. Possibly serveradmin or processadmin rights can let you see this, but someone would need to give you those rights.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
April 30, 2008 at 9:19 am
Let me know if this helps:
GRANT VIEW SERVER STATE to YourLogin
http://msdn.microsoft.com/en-us/library/ms186717.aspx
ask your sysadmins to give you the above rights. At least you will be able to open the Activity Monitor.
Regards,Yelena Varsha
April 30, 2008 at 3:03 pm
thanks this sounds exactly what i need, do you know what other permissions does this grant? also i could only view not make changes correct?
April 30, 2008 at 3:08 pm
Yes. There is another permission "ALTER SERVER STATE" , check out the link in my previous post. If you need more, maybe your sysadmin has to give you rights as Steve says the roles ProcessAdmin or ServerAdmin
Regards,Yelena Varsha
April 30, 2008 at 3:10 pm
thanks, no that's enough rights, i just want to be able to view. thank youuu
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply