SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 need help with an issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

agastyamahi
Starting Member

Canada
32 Posts

Posted - 01/11/2014 :  01:56:14  Show Profile  Reply with Quote
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
52317 Posts

Posted - 01/11/2014 :  02:28:10  Show Profile  Reply with Quote
what all roles are users mapped to in msdb database?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 01/11/2014 :  02:49:07  Show Profile  Reply with Quote
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,
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/11/2014 :  04:58:20  Show Profile  Reply with Quote
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
Go to Top of Page

agastyamahi
Starting Member

Canada
32 Posts

Posted - 01/11/2014 :  20:22:52  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/12/2014 :  01:58:53  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000