Importance of User Without Login – T-SQL Demo Script

In following demo Pinal 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 UserCREATE USER [testguest] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]
GO-- Step 3 : Checking access to TablesSELECT *FROM sys.tables;-- Step 4 : Changing the execution contestEXECUTE AS USER   = 'testguest';GO-- Step 5 : Checking access to TablesSELECT *FROM sys.tables;GO-- Step 6 : Reverting PermissionsREVERT;-- Step 7 : Giving more Permissions to testguest userGRANT SELECT ON [dbo].[ErrorLog] TO [testguest];GRANT SELECT ON [dbo].[DatabaseLog] TO [testguest];GO-- Step 8 : Changing the execution contestEXECUTE AS USER   = 'testguest';GO-- Step 9 : Checking access to TablesSELECT *FROM sys.tables;GO-- Step 10 : Reverting PermissionsREVERT;GO-- Step 11: Clean upDROP 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)