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.
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.