October 4, 2006 at 2:14 pm
1. I would like to issue viewing jobs privilege to a user from the Enterprise Manager. What is the best approach to do it?
2. I would like to issue execute all stored procedures to a user. What is the best approach to do it?
Any input will be greatly appreciated.
October 5, 2006 at 5:13 am
You can work around SQL 2000 security by granting a user membership of TargetServerRole, or in 2005 you can utilise the SQLAgentReaderRole.
For stored procedures you can use a GRANT statement using the type 'P' to specify procedure type objects in the sysobjects table.
*However*, you're far more likely to get a comprehensive solution if you elucidate on the problem you are trying to fix by doing this.
October 5, 2006 at 9:20 pm
1. Viewing job:
We can modify the TargetServerRole role and assign it to a user. But what if we need this role for more than one user with different privileges?
2. Grant Execute privilege:
Do we have to use a cursor, combining with something below to implement the task?
GRANT EXECUTE TO userName ON storedProcedureName
October 6, 2006 at 4:17 am
1 - Grant a group membership of that role, use other groups/roles for other permissions - mix and match to suit.
2 Use a while loop and run through the sysobjects table where type = P to get all the procedures
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply