give select writes to table to two users

[swhitten]

I am trying to grant select rights on a table to two users. I tried first going to the table in enterprise manager (SQL 2000) nand right clicking, then going to properties, then permissions. I check the select box for the two users and click apply. It sits forever and never gets finished. It shows 'not responding.'

So I tried right clicking table, all tasks, manage permissions. Same result. It has been sitting for 20 minutes, not finishing.

I've had this happen before, but it has always eventually finished. It has just never taken this long. Ideas? Alternatives?

 

[mrdenny]

Use the GRANT command

GRANT SELECT ON TableName to UserName

There is some sort of blocking going on which is preventing your command from finishing. Effectivly what SQL Server is doing is the same GRANT command. However it may be running other commands as well which are being blocked.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[swhitten]

I tried the script - great idea! Thank you! Unfortunately after executing for several minutes, still no luck. So I stopped the query, got all users out and stopped and restarted the sql server. After that, I was able to use Enterprise Manager to grant access.

The odd part is deeper, though. Why did these users lose access in the first place? User said report that depends on this access ran fine in January. When she tried to run it this week, it wouldn't run. These two users had lost select permissions. The only thing their IT guy will admit to is 'reindexing SQL'. But I don't know that doing so would've taken away rights. Only thing quirky - this is a Crystal report that pulls out of two different DBs.

Thanks again for your help.

 

[mrdenny]

Reindexing the database wouldn't have remove the rights. If the table was dropped and recreated that would have removed the rights.

Next time this happens, run the script and look at the bottom task bar in the app. It will tell you the SPID that you are using. Then open another window, and run sp_who2 with that spid after it. In the blocked column it will tell you what process is preventing your query from running.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog