Solved

Sql user canot use standard reports using MS SQL Management studio

Posted on 2013-06-20
6
1,455 Views
Last Modified: 2013-06-27
Hi!

A user using SQL authentication via MS SQL Management Studio would like to use some standard reports on some databases, however errors are returned.

e.g. All transactions report (the main one to be used) returns
"Unable to retrieve data for this report. Following error occurred.
Msg 297, Level 16, State 1
The user does not have permission to perform this action"

(The same happens if I connect using this authentication, however all works if I use my Windows Authentication)

I cannot identify needed permission.

Addition background:
server: MS SQL Server Developer Edition x64 2008 R2
OS: Windows 2008 R2 Enterprise edition x64

The sql user used to get the report is the owner of the database


Cheers, Damjan
0
Comment
Question by:DamjanDemsar
  • 4
  • 2
6 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 39262108
Login to http://localhost/Reports
Give permission to reports there
0
 
LVL 5

Author Comment

by:DamjanDemsar
ID: 39262130
Hmm, progress

It seems that I do not have permissions (however my username is both OS and SQL server administrator)

what permissions do I need, or how to check who has them?
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39262169
Try this link - it explains about Report Server Permissions
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 5

Author Comment

by:DamjanDemsar
ID: 39264993
Hmm, I managed t to  connect. Thanks about that.

Am I reading it right, that I cannot add sql authentication user into reporting services, or would that be basic authentication (just make sure username and password are the same)?

Cheers, Damjan
0
 
LVL 5

Author Comment

by:DamjanDemsar
ID: 39267635
OK, I just needed to find which user had initial permitions. It had to be the built in administrator account (no other local administrators worked).


Thanks for help.
0
 
LVL 5

Author Comment

by:DamjanDemsar
ID: 39280566
Hmm... just addional info....

It seems that if you try to run standard reports from MS SQL management studio then report server permissions do not apply. The connected user needs permissions on SQL server to VIEW ALL DEFINITION and VIEW SERVER STATUS

so open new query

use master;
GRANT VIEW ALL DEFINITION to username
GRANT VIEW SERVER STATUS to username
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL tables, .net application and ETL connundrum 3 54
SQL: launch actions one before the other 10 22
best counters for cpu high usage 3 28
Sql Stored Procedure 65 26
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question