December 7, 2016 at 8:53 am
Just wondering if anyone has some suggestions on standards for SSRS Report Server Configuration and Report Development?
I have come across suggestions like:
Use stored procedures and views in reports.
Avoid using 'Select * From'
Create report folders with logical business names in Report Manager; makes it easier to manage security.
Give reports a descriptive name.
Prefix stored procedures with 'ssrs', 'rpt', etc.
Prefix data sets with 'ds', data sources with 'cn' or 'conn'
For the Report Manager Data Source, I am using a sql server login (REPORTUser_Accounting) with the following option selected: 'Credentials stored securely in the report server'
Everything appears to be working just fine but want to make sure we (DBA and developers) are following some standards.
Two specific question regarding the prefixing of stored procedures and views with say 'ssrs', is this a good thing to do? I guess it would allow us to easily and visually determine which stored procedures are used in SSRS Reports. Is there a sql script that can be run to list stored procedures and views used in each SSRS report?
Thanks in advance.
December 7, 2016 at 1:38 pm
The points you've listed are good ideas.
I can't talk from experience regarding how best to use stored procedures for report queries, I can talk from the other side though. We have a large number of reports with queries in each - that's created a maintenance headache, particularly when server or table names change. We also miss out on stored procedure plan caches being used.
Other thoughts regarding this, you could use separate database schemas, and only allow REPORTUser_Accounting access to execute the procedures in that schema. A negative of this is it would require two-part naming (SELECT ... FROM dbo.Employee). This would also allow you to separate both report stored procedures and non-report stored procedures. If you're not doing that, yes, a "SSRS_" prefix makes sense.
A neat query to retrieve the Command Text (Stored Procedure calls or query text) from SSRS reports is here, under "Querying the RDL Contents"[/url].
A couple of other tips I've found through experience: Try to look for existing reports that can be modified to match the original and new requests before adding additional reports. Adding a parameter to a report where possible reduces the maintenance cost of having multiple similar reports. Particularly with Summary vs. Detail, it's easier to have a summary table and a details table in the same report, with visibility controlled by a "Show ... Details" parameter, unless you're dealing with huge numbers of rows.
We've had issues with employees not knowing the specific filters applied to reports, or which data source has been used. If you think that could be a problem, consider adding a report heading to your reports including the report title, a description, the data source, and the values of any parameters or query filters used.
December 12, 2016 at 6:20 am
What is the best way to give the sql login REPORTUser_Accounting permissions to grant execute permissions to stored procedures and select permissions on views? I think the db_securityadmin database role will work but that also provides more permissions than necessary from my understanding. I am trying to give least privileges to this account.
Thanks in advance.
December 12, 2016 at 7:45 am
Personally I have a db_executor role on our databases (I've added it to our model, so it's on all of our new ones too), and I have added our SSRS Service account to the role on the databases it needs.
USE DevTestDB;
GO
CREATE ROLE db_executor;
GRANT EXECUTE TO db_executor;
GO
ALTER ROLE db_executor ADD MEMBER [SSRS Service Acct];
GO
That will grant it access to execute all sp's on the database. If you therefore want it to be able to select from all views/tables, then just add it to the db_datareader role as well.
If you really want to limit it to what sp's and views it can access though, you'll need to GRANT it SELECT/EXECUTE privileges specifically on the objects you want it to have permission to(or add it as a member of the role(s), and then set DENY permissions to the user for specific ones you don't want it accessing).
Hope that helps.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2016 at 9:01 am
I am receiving the following error when I GRANT Execute on the stored procedure to the db role:
USE [DBName]
GO
GRANT EXECUTE ON [SchemaName].[StoredProcedureName] TO [DB Role];
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'stored procedure name', because it does not exist or you do not have permission.
I have read a lot on the internet regarding this error but have not found a solution. The GRANT EXECUTE works when I grant Control Permission to the sql login (REPORTUser_Accounting) or when I put the sql login in the db_securityadmin database role.
December 12, 2016 at 9:25 am
Did you add the user to the role? The message is giving you that error for a reason, that account can't execute that sp.
This, for example, works:
USE DevTestDB;
GO
CREATE USER [TestUser] WITHOUT LOGIN;
GO
CREATE PROCEDURE Test_sp AS
SELECT 1 AS ReturnValue,
USER_NAME() AS UserName;
GO
CREATE ROLE TestRole;
GRANT EXECUTE ON Test_sp TO TestRole;
ALTER ROLE TestRole ADD Member TestUser;
GO
EXECUTE AS USER = 'TestUser';
EXEC test_sp;
REVERT
GO
DROP PROCEDURE test_sp;
DROP USER TestUser;
DROP ROLE TestRole;
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 12, 2016 at 11:24 am
I may may not have asked this clearly in my earlier posts but what I really want to know is what is the least privilege method to allow a developer the ability to Grant Execute Permissions on Stored Procedures/Views to a database role or user. The REPORTUser_Accounting is the sql login used in our report server data source connection. On our 'play' server, I want to be able to allow developers to Grant Execute Permissions on Stored Procedures/Views (that they create) to a database role or to the REPORTUser_Accounting sql login. Therefore, the REPORTUser_Accounting sql login will have permissions to the developer's stored procedures/view via the report server data source connection.
While testing this method, I am receiving the following error when I log in as another user (EXECUTE AS LOGIN = 'Developer_User') and attempt to GRANT Execute on the stored procedure to the db role:
USE [DBName]
GO
GRANT EXECUTE ON [SchemaName].[StoredProcedureName] TO [DB Role];
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'stored procedure name', because it does not exist or you do not have permission.
I have read a lot on the internet regarding this error but have not found a solution. The GRANT EXECUTE works when I grant Control Permission (on Schema) to the developer login or when I put the developer login in the db_securityadmin database role. But, this may be giving up to many permissions.
I thought I tried GRANT EXECUTE and WITH GRANT Option:
GRANT EXECUTE ON SCHEMA::[SchamaName] TO [DeveloperLogin] WITH GRANT OPTION
I must be doing something wrong.
December 13, 2016 at 1:59 am
Ok, yes, this is a different question, what I've answered is how to give the Reporting Services Serive account execute privilegdes.
The db_securityadmin will allow a user to GRANT, DENY and REVOKE permissions, yes, but won't allow them to administer roles. It also doesn't give them access to any data. Is this what you're after?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 13, 2016 at 7:14 am
To your original question, I would add several comments on the reporting side of things. This first one has already been stated, but yes, if you have report parameters, _always_ have a report header (or footer) statement that states the parm value(s) to the end user.
Also, footnotes of explanation about the report and how to interpret results always help; even if no one else reads them, the report designer can at a glance see what is and is not being used in the report.
Another good standard/best practice is to have parameter drop down values. Occasionally we'll have report users request a blank box for comma delimited text (if multiple values), but that's the exception to the rule. And just as having the report use a stored proc instead of a SQL command inside the rdl is best, so also to supply the parm values via a stored proc (which can then be re-usable in other reports) is good.
There are other things that deal with more 'tips' than best practices, such as how to deal with a parameter referencing a column that allows NULLs, but I'll not go into that here.
Also, it's possible to use roles in Report Manager, in concert with Active Directory, to limit which report users see which reports. We've usually done that at a reporting folder or sub-folder level.
Best wishes!
Nate
December 13, 2016 at 1:31 pm
Here is what I am trying to accomplish. I would like to be able to give a developer the GRANT EXECUTE TO [Login or DBRole] WITH GRANT Permission so they would be able to GRANT EXECUTE Permissions on stored procedures and views to a another login or DBRole. (I can get this to work by assigning the developer to the db_securityadmin role or by Granting CONTROL on the schema to the developer. There may be some security risk with these two methods and they are not what you call the practice of least privileges. Please share any security risks associated with these two methods.)
--The first SSMS connection:
USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD=N'???????';
GO
USE [DBName]
GO
CREATE USER [Test] FOR LOGIN [Test] ;
GO
USE [DBName]
GO
GRANT EXECUTE TO Test WITH GRANT OPTION;
Go
USE [master]
GO
CREATE LOGIN [Test1] WITH PASSWORD=N'???????'
GO
USE [DBName]
GO
CREATE USER [Test1] FOR LOGIN [Test1] ;
GO
USE [DBName]
GO
ALTER ROLE [db_datareader] ADD MEMBER [Test1]
GO
--In second SSMS connection (connected with Test login):
USE [DBName]
GO
GRANT EXECUTE TO Test1 WITH GRANT OPTION
Go
--Back to the first SSMS connection:
-- Set the execution context to Test1.
EXECUTE AS LOGIN = 'Test1';
--Display current execution context.
SELECT SUSER_NAME(), USER_NAME();
USE [DBName]
GO
GRANT EXECUTE ON [Schema].[StoredProcedureName] TO [DBRole];
--Msg 15151, Level 16, State 1, Line 1
--Cannot find the object 'StoredProcedureName', because it does not exist or you do not have permission.
December 14, 2016 at 1:57 am
You're trying to grant a user permissions when you do not have permission do do so. The db_executor role I gave you gives the user permission to EXECUTE, which was the answer I gave based on your original, now changed, question.
As i said before, to GRANT/DENY/REVOKE permissions a user should have the db_securityadmin role. This enable them, unsurprisingly, to GRANT/DENY/REVOKE permissions for a user. It does not enable to manage database roles, SELECT/DELETE/EXECUTE/ETC. They also cannot create Logins on the server, so the only thing they could do would be grant themselves access to views, or grant other people, which should all be be monitored, if you are that security conscious.
p.s. Try to post all your responses here. PMing me doesn't give others the full picture.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply