Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
PapillonUK
Starting Member
22 Posts |
 Posted - 2004-07-27 : 08:02:18
|
| I would like to allow a user to see if a particular job is enabled using:SELECT enabled FROM msdb.dbo.sysjobs WHERE name = 'Job Name'Obviously, i get access denied on object 'sysjobs', database 'msdb', owner 'dbo'.So, i thought i'd encapsulate the SELECT statement in a stored procedure and give the users EXEC rights to THAT.This works on objects in my main database.Why do I get the same access denied error on the table and, other than specifically granting access to msdb.dbo.sysjobs for the user (ugh!), how do i get around this problem?Thanks for any adviceSime |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-27 : 08:12:21
|
| Because EXEC doesn't work for cross-database permissions, not even in your main databases. You could set up a procedure in the msdb database and give them permission/access to that. You could also setup a view in the user database they could look at.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
PapillonUK
Starting Member
22 Posts |
Posted - 2004-07-27 : 08:40:25
|
| I liked the view idea as that avoids me having to give the user access to the msdb database.I created a view in the user database with the same SELECT statement.Sure enough it worked when logged in as sa.I gave the user SELECT permissions on the view.When i select from the view as the user, i still get the same message however:SELECT permission denied on object 'sysjobs', database 'msdb', owner 'dbo'.I even tried creating a sp in the user db to SELECT from the view...same message!!!Looks like there's no alternative but to mess up the msdb database with the username and a stored procedure. I don't like this, both from an admin and security point of view so i'll hold fire until i can think of a better way? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-07-27 : 08:49:40
|
| Set up the view in the msdb database and give them SELECT permissions. Then setup the procedure to look at that view from the user database.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
PapillonUK
Starting Member
22 Posts |
Posted - 2004-07-27 : 09:24:16
|
Yes that works, thanks - better to have only a view in msdb.I can work with that for now, but it still means i have to give the user access to msdb and i'm also not keen on creating objects in databases other than my own (thinking of the extra hassle involved in moving databases between servers)Wonder if there's any other approach that would circumvent these issues? eg. is it possible to run a sp which can use a sysadmin account as a proxy for its duration? Hmmm? Fussy? moi?  |
|
|
|
Pat Phelan
Posting Yak Master
187 Posts |
Posted - 2004-07-27 : 13:51:13
|
quote:
Originally posted by PapillonUK
Wonder if there's any other approach that would circumvent these issues? eg. is it possible to run a sp which can use a sysadmin account as a proxy for its duration? Hmmm? Fussy? moi?
You can proxy access using an extended stored procedure (xp_), but not using a conventional stored procedure.-PatP |
|
|
|
|
|
|
|
|
Allow users access to sys tables via stored proc