Whitelisting SQL Server Access

Patrick Keisler has a script to whitelist access to SQL Server:

A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.

The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.

This is an interesting concept.  Resource whitelisting makes sense, though we tend only to use authentication-based whitelisting (i.e., creating logins).

Related Posts

Auditing Database Backups

Jovan Popovic shows how you can audit who is taking backups on an Azure SQL Managed Instance: One mechanism to ensure that nobody can take the COPY_ONLY backup of your database is to use Transparent Data Encryption that automatically encrypts all backups. In that case you would need to use Customer-managed (BYOK) TDE where you will keep […]

Read More

When xp_logininfo Fails

Gianluca Sartori helps Future Gianluca (and present us in the meantime) troubleshoot issues with xp_logininfo: The user does not existThis is very easy to check: does the user exist in Windows? Did you misspell the name? You can check this from a cmd window, issuing this command: net user SomeUser /domain If you spelled the […]

Read More

Categories