Permission Setting Hangs?

[evasyar]

I am having issues granting permission to a particular table for a particular ROLE, when i run:
USE PLUMDB
GO
GRANT SELECT
ON PLUMDB.PLUMUSER.PTSERVERCONFIG
TO COLLABREAD
GO
from Query Analyzer or I use EM to manually set the permission, it hangs indefinitely, does not error out or anything, it just hangs... Inside Query Analyzer, I just end up cancelling out the query since it just sits there...

Here's the thing that is weird tho, it is just on this particular table in this database, every other user-defined tables in there I can GRANT just fine... Just this particular table.

Our app is a .NET web portal, and this app is using a clustered active/passive relationship setting with a storage data array and two database servers. We have basically restored a backup of our UAT instance of the databases to our PROD instance of databases, including all objects and user accounts.

Can someone help me out troubleshoot to confirm what I am missing in diagnosing this peculiar prob?

 

[jgoodman00]

Have you checked the locking when you try to run this? It could be waiting for a lock to be freed before it can execute.

You can view the locks from EM, or from QA using sp_lock

James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[evasyar]

hello jgoodman00,

thanx a million brother, sure enough the very table I have been trying to grant access to has a GRANT lock. However, please excuse my ignorance on the subject of LOCK, but how would one "unlock" this setting???

Ray Salgado

 

[evasyar]

to jgoodman00 or anyone,

could this query trans pretty much resolve my GRANT setting from being hanged

USE PLUMDB
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
BEGIN TRAN
GRANT SELECT
ON PLUMUSER.PTSERVERCONFIG
TO COLLABREAD

??? Ray Salgado

 

[jgoodman00]

You should not need to configure locking for permission setting.

I was referring to locks on the system tables, perhaps by some kind of maintenance procedure.

Try running the original procedure with profiler running, & then examining the profiler trace. This might give you some insight into what is happening...



James Goodman MCSE, MCDBA

http://www.angelfire.com/sports/f1pictures

 

[evasyar]

hello jgoodman00,

I did not see clearly what transaction in profiler blocks our grant setting, however, we have seen the actual process that blocks our grant process and ID'ed the application whose connection blocks our GRANT process to get through.

our plan is to bring down that server that executes that app (our TomCat server, TomCat is the one initiating a block) temporarily, execute the GRANT in the DB, then bring back that server. hopefully this resolves our portal server problem.

anyways thanks a lot for pointing me to some of the diagnostics tools that i should be looking at. let you know if this plan of action succeeds.

ray