Is it possible to access the syslockinfo table in SQL 2005, once an application role has been activated?

• Mark Campbell-279903

  SSC Veteran

  Points: 269

  More actions

  March 21, 2006 at 4:24 pm

  #168184

  I am working on software which uses application roles to allow access to the database.  The application queries master..syslockinfo.  The application works correctly in sql server 2000.  However, in sql 2005 a permission not allowed message is displayed.  Does anyone know of anyway to grant rights to an application role to allow queries against master..syslockinfo?  

  I thought about switching the code from

  select rsc_objid from master.dbo.syslockinfo

  to

  select rsc_objid from master.sys.syslockinfo

  but I have not tried this, yet.

   

  I am also going to see if I can find any views which allow this information to be retrieved.

  Thanks in advance for any information.

• Joseph Mulhall

  SSCarpal Tunnel

  Points: 4672

  More actions

  March 22, 2006 at 4:05 am

  #628044

  Grant select on sys.dm_tran_locks to applicationrole

  --Lock handling shouldn't be written into the application.

• Mark Campbell-279903

  SSC Veteran

  Points: 269

  More actions

  March 22, 2006 at 10:19 am

  #628165

  I created an application role named testit.

  Then I executed:

  Grant select on sys.syslockinfo to testit

  However, I still receive the message

  "The user does not have permission to perform this action", if I execute the following statement after activating the application role:

  select rsc_dbid  from sys.syslockinfo with (nolock)

• Mark Campbell-279903

  SSC Veteran

  Points: 269

  More actions

  March 23, 2006 at 4:42 pm

  #628489

  I understand now.  It is recommended to use sys.dm_tran_locks, instead of sys.syslockinfo.

  However, I tried granting rights to sys.dm_tran_locks and I still received the same error message. 

  Thanks!