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.
- Login using the System Admin account
- Create a user without login
- Checking Access
- Impersonate the user without login
- Checking Access
- Revert Impersonation
- Give Permission to user without login
- Impersonate the user without login
- Checking Access
- Revert Impersonation
- Clean up
USE [AdventureWorks2012] GO -- Step 1 : Login using the SA -- Step 2 : Create Login Less User CREATE USER [testguest] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo] GO -- Step 3 : Checking access to Tables SELECT * FROM sys.tables; -- Step 4 : Changing the execution context 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 context 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 (https://blog.sqlauthority.com)