![adventureworks2012 code count adventureworks2012 code count](http://stevestedman.com/wp-content/uploads/Visualization_Google1.png)
Let’s say you have a company policy that prohibits anyone from using a common login to connect to a SQL Server.
![adventureworks2012 code count adventureworks2012 code count](https://i.stack.imgur.com/DuRdL.jpg)
So why would we care about this? Why not just manage access to SQL Server the normal way with logins and passwords. In our example, that “special consideration” is access to the SQL Server only if your workstation IP address is on the whitelist. According to the American Heritage Dictionary, it is “a list of people or organizations that have been approved to receive special considerations”. So let’s start off by defining “whitelist”. Today, I wanted to demonstrate how you can accomplish the same thing with your on-premise SQL Servers by creating a whitelist. In my previous post, Configuring the Azure SQL Database Firewall, I discussed how you can configure the firewall rules to manage access to the SQL Server. UPDATED – – Please review the updated code here. WHERE ((LoginName = OR (LoginName = '*'))ĪND((HostIpAddress = OR (HostIpAddress = '*'))
![adventureworks2012 code count adventureworks2012 code count](http://lh6.ggpht.com/_0j4bzarlOBg/TWYwJVGi7uI/AAAAAAAAB2U/_djHFjcxm80/countrowssqlserver_thumb[4].png)
ON ALL SERVER FOR VARCHAR(255) = VARCHAR(255) = VARCHAR(50) = CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address')) VALUES ('*','ECHOBASE1','*','Any user from the workstation "ECHOBASE1" is allowed to connect, regardless of IP address.') INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress,Comments) IF OBJECT_ID('dbo.WhiteList') IS NOT NULL The idea is to provide some documentation about what the white-listed item is attempting to do. I also added another column to the WhiteList table that can be used for hold comments. Please let me know if you run into any other issues with this new version. If I had followed my own rules, I could have discovered most of these issues before posting the original article.