SQL Server database roles and GDPR

Corporate Audits and other regulatory  initiatives such as GDPR  , it is increasingly important to apply solid database access security practices. Users should only be able to  view approved data .  Avoid applying wider roles to users , such as db_owner when all the user needs is some access to 2 tables and a stored procedure. 

One of the tools available to the DBA is the database role. The principle of the database role is basic , and any sql server database comes with a set of default database roles such as db_datareader and db_owner. Always using the default database roles may establish access patterns which are wider than allowed for a user. 

The database role - allows a more granular control on access to database objects. 

In this example , a login and database user are created , and mapped to a database role which grants execute rights to the stored procedure :sp_test. 

USE [master]
GO
CREATE LOGIN [test_usr] WITH PASSWORD=N'!ZcTuTes', DEFAULT_DATABASE=[MYDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [MYDB]
GO
CREATE USER [test_usr] FOR LOGIN [test_usr]
GO
USE [MYDB]
GO
CREATE ROLE [test_role] AUTHORIZATION [dbo]
GO
use [MYDB]
GO
GRANT EXECUTE ON [dbo].[sp_test] TO [test_role]
GO
USE [MYDB]
GO
ALTER ROLE [test_role] ADD MEMBER [test_usr]
GO

Read more on database roles and security best practices 

SQL Database roles and members

SQL Server - Grant execute on all stored procedures

How to manage the IS_GRANTABLE security risk (SQL Server DBA)

SQL Server Security Violations Report for t-sql tuesday