SQL Snippets: Create and Use Database Role to Restrict Access

This post is part of the series on SQL Snippets.

I create a lot of SQL objects such as views, tables, stored procedures and so on. If they will be used from within Microsoft Dynamics then they can simply be assigned to the DYNSA role, but if they are for use in custom reporting or integrations then separate security will be required.

Usually for reporting, and especially for integrations, security will be as locked down as possible to minimise any possible attack vector. This can be done in SQL through a security role with only limited security granted to the necessary users.

The first step is to create the role itself; I always do this using a role with a name prefixed with urpt_ so that it can easily be identified as a custom user role:

-- creatr security role on database
CREATE ROLE [urpt_Role] AUTHORIZATION [dbo]
GO

Then we grant the relevant permissions to the new role. For a report this would mean only granting select permissions on the relevant views and tables; for an integration there may be inserts and updates granted.

-- grant permission to role on object
GRANT SELECT ON [dbo].[uv_SQLView] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL20000] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL30000] TO [urpt_Role]
GO

For a report, you may need to create a database user for the user or AD group:

-- create user on database
CREATE USER [DOMAIN\user]
GO

The final step is to assign the role to the relevant users or AD groups:

-- assign role to user on database
ALTER ROLE [urpt_Role] ADD MEMBER [DOMAIN\user]
GO