How to prevent SysAdmins from accessing specific tables in SQL Server

Aman Raiyyani
How to prevent SysAdmins from accessing specific tables in SQL Server

There are several ways to prevent sysadmins from reading data from specific databases in SQL Server, but one common approach is to use database roles and permissions.

Create a new role: Create a new role, such as “db_restricted_reader” with SELECT permissions on the specific database.

USE mydatabase;

CREATE ROLE db_restricted_reader;

GRANT SELECT ON mydatabase TO db_restricted_reader;

Assign the role to non-sysadmin users: Assign the role to the non-sysadmin users that need access to the database.

USE mydatabase;
EXEC sp_addrolemember 'db_restricted_reader', 'user1'
EXEC sp_addrolemember 'db_restricted_reader', 'user2'

Remove sysadmin users from the role: Remove the sysadmin users from the role to prevent them from accessing the data.

USE mydatabase;
EXEC sp_droprolemember 'db_restricted_reader', 'sysadminuser1'
EXEC sp_droprolemember 'db_restricted_reader', 'sysadminuser2'

This way, sysadmin users will not be able to access the data in the specific database, even if they have sysadmin privileges on the server. It’s important to test the implementation in a test environment before moving it to production.

It’s also important to ensure that the permissions are working properly and to keep them updated. It’s also important to monitor the access to the database and to log the accesses to detect any unauthorized access.

It’s also important to note that, it’s not recommended to rely solely on this method to protect sensitive data, and it’s highly recommended to use other security measures such as encryption, secure communication protocols, and access control mechanisms.

Explore our range of trailblazer services

Risk and Health Audit

Get 360 degree view in to the health of your production Databases with actionable intelligence and readiness for government compliance including HIPAA, SOX, GDPR, PCI, ETC. with 100% money-back guarantee.

DBA Services

The MOST ADVANCED database management service that help manage, maintain & support your production database 24×7 with highest ROI so you can focus on more important things for your business

Cloud Migration

With more than 20 Petabytes of data migration experience to both AWS and Azure cloud, we help migrate your databases to various databases in the cloud including RDS, Aurora, Snowflake, Azure SQL, Etc.

Data Integration

Whether you have unstructured, semi-structured or structured data, we help build pipelines that extract, transform, clean, validate and load it into data warehouse or data lakes or in any databases.

Data Analytics

We help transform your organizations data into powerful,  stunning, light-weight  and meaningful reports using PowerBI or Tableau to help you with making fast and accurate business decisions.

Govt Compliance

Does your business use PII information? We provide detailed and the most advanced risk assessment for your business data related to HIPAA, SOX, PCI, GDPR and several other Govt. compliance regulations.

You May Also Like…