Create a Database Role to Grant Access to Views for Reporting

Too many times as a consultant I have discovered that users have been grant full access to a database when they only need select permissions on a handful of SQL views or tables. Whenever I create a new SQL view for a client I will create a database role to go with it, as best practice is to only grant the minimum permissions needed.

Below is a script example of creating a database role and then adding a couple of views in with select permissions; tables would be added in exactly the same way.

Once the role has been created you can assign it to any of the users required to have access to the objects to which it is granting access.

-- CREATE ROLE
CREATE ROLE rpt_AZRCRV_Reports

GO
-- ADD SELECT PERMISSIONS FOR VIEWS TO ROLE
GRANT SELECT ON uv_AZRCRV_POReqApprovalStatus TO rpt_AZRCRV_Reports

GRANT SELECT ON uv_AZRCRV_POPOrderApprovalStatus TO rpt_AZRCRV_Reports

GO

A role for stored procedures or function can be created in exactly the same way; the only difference is that you would be assigning EXECUTE permissions instead of SELECT.

Read original post Create a Database Role to Grant Access to Views for Reporting at azurecurve|Ramblings of an IT Professional