July 13, 2017 at 3:44 pm
On an SSRS 2008 R2 report, I am getting the following error when I run the report in Visual Studio,
"The EXECUTE permission was denied on the object 'Dataset1', database 'OPSDataStore', schema 'dbo'.
Dataset1 is a dataset in the report and contains embedded sql. When I run the embedded sql on sql management studio, everything runs fine.
However when I run the report, I get the error I just listed above. The dba says that I am assigned as dbo owner on the test database. Thus this should not be a database error. The error has something to do with the ssrs report..
I am getting this error message on userid parameter value. I am using the following for the parameter value
= right(User!UserID,10)
Thus would you tell me what you suggest that I look at to see where the proiblem actually lies?
July 13, 2017 at 4:25 pm
I think your Dataset1 is set up odd.
The way I am reading that error is that your Dataset1 has the text "EXEC dbo.Dataset1", which I imagine is not what it says.
If your dataset1 is executing some SQL stored procedure (ie EXEC dbo.Dataset1), then you may be missing EXECUTE permissions on that.
Your DBA should be able to run profiler and tell you exactly what is being run from your Visual Studio aginst the database and that should help point you in the correct direction.
I would also check what account your dataset1 is connecting as. This should be in your datasource. It could be you have permissions but the account in your datasource does not. OR it could be that your datasource is pointing to the live SQL instance which you may not have permissions on.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 13, 2017 at 4:39 pm
What are the credentials being used for the Data Source for the report, what are the settings for the options in Connect Using section?
And also check the connection string in the top of that window - check the initial catalog. And did you test the connection when viewing the data source?
Sue
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply