Select permissions on Master db SysCacheObjects

• George H.

  SSCommitted

  Points: 1522

  More actions

  April 2, 2008 at 12:05 pm

  #180522

  Hello,

  I'm using a script I grabbed from this site that indicates whether a PROC is currently running or not. It queries the master..syscacheobjects table.

  How do I set up the security and permissions so that users (using one, general SQL login) can get permissions to run the PROC and have it select off the master table. I keep getting "Select permission denied on Master.dbo.SysCacheObjects Table" messages.

  So to recap, I have a PROC in my app DB that selects from the MASTER syscacheobjects table. How should I set up the permissions to prevent the general app SQL login account from getting select permissions to the MASTER table?

  Thanks,

  George H.

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  April 8, 2008 at 8:48 pm

  #800113

  Did you try to grant SELECT permission to PUBLIC?

• George H.

  SSCommitted

  Points: 1522

  More actions

  April 9, 2008 at 4:29 am

  #800262

  Well, what I ended up doing is created a new role in the master db, then added my app's login to that role, then gave that role only select permissions on the syscacheobjects table. I didn't want to give it to public (I'm kinda paranoid about giving out permissions to the public role).

  I just wasn't sure if there was a generally accepted secure way to do this.