SQL Server database roles and GDPR

05 October,2018 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server database roles and GDPR


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer