When it comes to security in SQL Server, there are always new concepts and techniques to learn. Recently, I had the opportunity to learn about restricting access to sensitive data from my friend Vinod. He shared an interesting scenario where you can have near-DBA privileges while still masking the data from the DBA.

In SQL Server, the sysadmin role has access to everything within the database. However, there may be cases where you want users to have DBA privileges but restrict their access to certain tables or schemas containing sensitive information. This is where user-defined server roles come into play.

In SQL Server 2012, a new feature called user-defined server roles was introduced to address this scenario. With user-defined server roles, you can create a role that has specific permissions and then add users to that role. This allows you to grant DBA privileges to users while still controlling their access to sensitive data.

Let’s walk through an example to see how this works:

1. Create a Sales database, logins, and users.
2. Create a schema called “CRM” and a table called “Employees” within the CRM schema. This table will contain sensitive information.
3. Create a server-scoped user role called “DBA Role” and add the DBA user to it.
4. Deny select permission on the CRM schema to the DBA user.

By following these steps, even though the DBA user has CONTROL SERVER permissions through the DBA Role, they will not be able to select the sensitive information from the CRM schema.

It’s important to note that the difference between a sysadmin and someone with CONTROL SERVER permission is that SQL Server will respect a DENY statement on an object, even when the user has CONTROL SERVER permissions. This means that even though the DBA user has CONTROL SERVER permissions, they will still be denied access to the sensitive data if a DENY statement is applied to their user account.

Restricting access to sensitive data is an important aspect of database security. By leveraging user-defined server roles in SQL Server, you can grant DBA privileges to users while still controlling their access to sensitive information. This ensures that only authorized individuals can view and modify sensitive data, providing an additional layer of security for your database.

Have you ever implemented this type of security measure in your SQL Server environment? I would love to hear about your experiences and any additional insights you may have on this topic.

Reference: Pinal Dave (https://blog.sqlauthority.com)