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 User without Login
Published: Dec 05, 2020
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:

use AdventureWorks2012
go

create login Bob with password = N'Bob'
go

create user Bob for login Bob
go

grant select on sales.salesOrderHeader to Bob
go


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…

create user BobProc without login;

grant select on sales.salesOrderDetail to BobProc
go

drop procedure if exists dbo.bobSalesCheck
go

create procedure dbo.bobSalesCheck
(
    
@salesOrderID int
)
with execute as 'BobProc'
as
    if
exists
    (
        
select *
        
from sales.SalesOrderDetail
        
where SalesOrderID = @salesOrderID
        
and ProductID = 776
    
)
    
begin
        select
1 as result
    
end
    else
    begin
        select
0 as result
    
end
go

grant execute on dbo.bobSalesCheck to Bob
go


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

execute as user = 'Bob'

select top 10 * from sales.SalesOrderDetail

revert
go



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

execute as user = 'Bob'

exec bobSalesCheck @salesOrderID = 43659
exec bobSalesCheck @salesOrderID = 43660

revert
go



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. 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.