find blocks without sysadmin rights

Question

find blocks without sysadmin rights

DBA-640728

SSChampion

Points: 12898
More actions
April 30, 2008 at 7:56 am

#181755

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 1

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.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

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

DBA-640728 SSChampion Points: 12898 More actions · Answer 3

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?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

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

DBA-640728 SSChampion Points: 12898 More actions · Answer 5

thanks, no that's enough rights, i just want to be able to view. thank youuu