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;
GO
CREATE ROLE db_restricted_reader;
GO
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;
GO
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;
GO
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.
