Earlier I wrote a blog post about SQL SERVER – Importance of User Without Login and my friend and SQL Expert Vinod Kumar has written excellent follow up blog post about Contained Databases inside SQL Server 2012. Now lots of people asked me if I can also explain the same concept again so here is the small demonstration for it. Let me show you how login without user can help. Before we continue on this subject I strongly recommend that you read my earlier blog post here.

In following demo I am going to demonstrate following situation.

1. Login using the System Admin account
2. Create a user without login
3. Checking Access
4. Impersonate the user without login
5. Checking Access
6. Revert Impersonation
7. Give Permission to user without login
8. Impersonate the user without login
9. Checking Access
10. Revert Impersonation
11. Clean up

USE [AdventureWorks2012]
GO
-- Step 1 : Login using the SA
-- Step 2 : Create Login Less User
CREATE USER [testguest] 9ITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO
-- Step 3 : Checking access to Tables
SELECT *
FROM sys.tables;
-- Step 4 : Changing the execution contest
EXECUTE AS USER   = 'testguest';
GO
-- Step 5 : Checking access to Tables
SELECT *
FROM sys.tables;
GO
-- Step 6 : Reverting Permissions
REVERT;
-- Step 7 : Giving more Permissions to testguest user
GRANT SELECT ON [dbo].[ErrorLog] TO [testguest];
GRANT SELECT ON [dbo].[DatabaseLog] TO [testguest];
GO
-- Step 8 : Changing the execution contest
EXECUTE AS USER   = 'testguest';
GO
-- Step 9 : Checking access to Tables
SELECT *
FROM sys.tables;
GO
-- Step 10 : Reverting Permissions
REVERT;
GO
-- Step 11: Clean up
DROP USER [testguest]Step 3
GO

Here is the step 9 we will be able to notice that how a user without login gets access to some of the data/object which we gave permission.

What I am going to prove with this example? Well there can be different rights with different account. Once the login is authenticated it makes sense for impersonating a user with only necessary permissions to be used for further operation. Again this is very basic and fundamental example. There are lots of more points to be discussed as we go in future posts. Just do not take this blog post as a template and implement everything as it is.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Security, SQL Server, SQL Tips and Tricks, T SQL, Technology