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 |
|
agastyamahi
Starting Member
33 Posts |
 Posted - 2014-01-11 : 01:56:14
|
| Hi,I wanted to get the last processed date and time of the cube, so I created a stored procedure in a database named DBS. In the stored proc, I am using sysjobhistory table and selecting the most recent date and time. The query was working perfectly fine.Later, I opened excel and connected to the db server and that particular db. Since I am the admin for the server, it was working fine for me, but for other users it was not working. The error for other users says select permission denied on object 'sysjobhistory' database 'msdb' schema 'dbo'. The user has access to the DBS database and cross db connection is enabled in both DBS and msdb databases, but I am still not understanding what the issue is. Can any one help me ?Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-11 : 02:28:10
|
| what all roles are users mapped to in msdb database?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2014-01-11 : 02:49:07
|
| In MSDB database: I didn't map to any role, when I mapped it as dbowner, then it was working but I was told that I shouldn't give dbowner access to any oneIn DBS database: I added that user and gave them execute permissions to that particular stored proc, |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-11 : 04:58:20
|
quote:
Originally posted by agastyamahi
In MSDB database: I didn't map to any role, when I mapped it as dbowner, then it was working but I was told that I shouldn't give dbowner access to any oneIn DBS database: I added that user and gave them execute permissions to that particular stored proc,
you need to map to datareader if they want to read contents of msdb tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
agastyamahi
Starting Member
33 Posts |
Posted - 2014-01-11 : 20:22:52
|
| Hi Visakh,In DBS database: I mapped the data reader on for that user and tried it out, it didn't work, but in MSDB db I mapped datareader on for the guest account and tried, it worked then. Can we map the data reader ON for the guest account in MSDB database ? Please let me knowThanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2014-01-12 : 01:58:53
|
quote:
Originally posted by agastyamahi
Hi Visakh,In DBS database: I mapped the data reader on for that user and tried it out, it didn't work, but in MSDB db I mapped datareader on for the guest account and tried, it worked then. Can we map the data reader ON for the guest account in MSDB database ? Please let me knowThanks
If you need the account to read data from msdb objects then you need to assign them to datareader role in MSDB------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
need help with an issue