| Author |
 Topic  |
|
|
agastyamahi
Starting Member
Canada
33 Posts |
 Posted - 01/11/2014 : 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
India
52326 Posts |
|
|
agastyamahi
Starting Member
Canada
33 Posts |
Posted - 01/11/2014 : 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 one
In DBS database: I added that user and gave them execute permissions to that particular stored proc, |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 01/11/2014 : 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 one
In 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 MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
agastyamahi
Starting Member
Canada
33 Posts |
Posted - 01/11/2014 : 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 know
Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 01/12/2014 : 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 know
Thanks
If you need the account to read data from msdb objects then you need to assign them to datareader role in MSDB
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
| |
Topic |
|
need help with an issue