Create User without Login

Following on from my last post in which I explained the difference between a Login and a User, I’m going to expand a little and explain the concept I use quite a lot within some of my posts… the “login-less user”.

As we know from the previous post, a Login provides access to the server whereas a User is the access to a database. Based on that it’s easy enough to ascertain that a Login-less user is an account which has database permissions but cannot login in to server as a whole.

Sounds weird, but there are valid reasons for doing this.

So let’s look at my Surface… I have no logins other than system and myself:

So let’s create a login and a user with VERY limited permissions on the AdventureWorks database:

Okay, we now have our user with very limited permissions because we’ve, correctly, determined that this is all he should be allowed to see.

So, what happens if we were to say that Bob needs to be able to occasionally check whether an order contained ProductID 776? We don’t want to give him access to the Sales.SalesOrderDetails table but he needs access in order to make the check?

Well how about this… we make a stored procedure which can make the check and only return a true or false (thereby hiding any secure Order Detail information), we give the stored procedure permissions to read the required tables, and we give Bob permissions to run the stored procedure?

This is exactly why we have Login-less Users…

So now we can run our checks as Bob whilst still having no actual access to the sales.salesOrderDetail table:

BUT we can access it through our stored procedure via the login-less user:

Hence this is now a completely secure method of granting specific access to information without actually allowing users to see the underlying data.

Leave a Comment

Your email address will not be published. Required fields are marked *