Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Create a Login with no User
Published: Dec 01, 2020
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
go

drop database myEmptyDB
go



Exactly what we wanted.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.