Continuing the theme of Users and Logins, I was asked if there was a circumstance in which you would ever require a Login without an affiliated User? The answer is yes, and it’s more common than you think.
There are a lot of people out there who have roles where the emphasis is on monitoring and maintaining the SQL Servers themselves and not touching the databases or code running on the servers. In these cases there would be no need to allocate access to the databases and data. In fact doing so could be seen as a severe security breach. Therefore these guys are given Server level access only and would require no User attached to their logins.
As an example, what if I’m in a really small company with no DBA and don’t want my SQL Developers to have permissions to create or drop databases? Therefore as a security precaution any new databases must be created by someone in Infrastructure instead, but we don’t want that person to be able to see any sensitive data within any databases. Therefore we can create them a login which allows them to create a database but without any attached user… this then stops them from seeing any databases at all, yet still allows them to do their allocated task.
Now we can connect to SQL Server just fine… but we can’t see anything about any of the databases:
However, we can still create (and drop) databases without issue:
create database myEmptyDB
drop database myEmptyDB
Exactly what we wanted.