SQL SERVER – Importance of User Without Login – T-SQL Demo Script

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] 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 (http://blog.sqlauthority.com)

About these ads

4 thoughts on “SQL SERVER – Importance of User Without Login – T-SQL Demo Script

  1. Hi Pinal

    It will work on the sql server 2008 R2, it is very useful to us at the time of db failover in DB Mirroing some of the logins and user are not mapped at that time better we can use only users

    can you please suggest us it is work in 2008 we will implement it. Becuase every DB automatic/ manual failover we facing alote of issues with logins and users

    Regars
    Siva

  2. @Siva,

    Database Mirroring is a disaster recovery solution and to add more its a database solution and not a server solution.

    Since it is a database solution all users inside a database already mirrored but logins are maintained at server level(master db to be specific) and logins are not mirrored. Same case with Log Shipping also.

    To address this issue, I created a daily evening SQL job that sync windows logins between principal and mirroring server. You can also do this real time by using DDL Triggers for “Create Login” Event.

    ~ IM.

    • To add to that, during the Instance-level login copy, don’t forget to copy the SID of the user across too. Otherwise the logins won’t work either!

  3. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s