Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

SQL Server Security Basics: Logins vs. Users

 When I discuss SQL Server security, one of the basic concepts I concentrate on is the difference between logins and users with respect to SQL Server. As of SQL Server 2005, Microsoft introduced new terminology, that of server and database principals. So the mappings look like:

  • login = server principal
  • user = database principal

However, when you look at the T-SQL given to create these objects within SQL Server, and the T-SQL was also introduced in SQL Server 2005, you find that they are CREATE LOGIN and CREATE USER respectively. So logins and users will remain a part of the nomenclature for the foreseeable future. Because of this, and because we are often very ambiguous when we use the term USER, I looked for a way to better communicate what SQL Server means when it uses LOGIN and when it uses USER. I finally came up with the self-storage facility model, which seems to work well. For instance:

Self Storage Facility model

 

In order to get into most self-storage facilities, you usually have to enter a code. For instance, there's a keypad at the gate of one such self-storage facility.

Gate Passcode

You can see the individual self-storage units inside. If the facility itself is considered the SQL Server and the individual self-storage units are the databases, then the first thing you must do is enter the facility. You do so by typing in the passcode. This is the equivalent to having a login. A login allows you to enter the SQL Server. However, that doesn't mean you have any access to any of the individual self-storage units, aka databases. That's because when we do a close-up on the storage units, we see:

Individual Self Storage Unit

Along the right side there's the place for a lock (or multiple locks) to go. We'll assume just one. In order to get into the unit, you have to be able to open the lock. In the real world that means either having the key or having the combination. Once you open the lock, you can enter the facility. This corresponds to having a user account in the database. For each database where you have the "keys" (you are a user), you can enter.

So what about master, msdb, and tempdb? Well, in those cases everyone comes in as guest (with the exception of members of the sysadmin role or anyone who has been granted CONTROL SERVER rights, in which case you come in as dbo). That's easy to fit into this model, too. Those are storage units where there is no lock. Everyone who can enter the server can get into them. And that's by design.

Now once you're in the database, there are permissions at the schema level, on the individual objects, etc. Once you get into a storage unit, there's nothing stopping someone from putting a safe, a locked box, or some other secured storage container in place. If you think of tables, stored procedures, schemas, and the like as those individual components that can fit inside the storage unit, that completes the model.

 

Comments

Posted by jsterrett on 21 April 2009

Brian this is a great post.  I think you drive home the difference between user and login.

Posted by Andy Warren on 22 April 2009

Agree - nicely done!

Posted by Jerry Hung on 22 April 2009

Nice graph - a picture IS worth a thousand words

Posted by Anonymous on 28 December 2009

VSDBPro Permissions part 1, Logins and Users

Posted by Ather Murtuzapurwala on 3 June 2010

nice article...

This means a user should always have login...to enter the main gate..but reverse is not true...is my understaning is correct?

Posted by seetha siva on 5 June 2010

Wonderful article,

I was just looking for a clear and simple example for this confusing analogy and there you had it. Thanks!

I have an extension question though. Now I hope one login cannot have multiple users assigned to it, so can we say even though multiple users can have access to same storage unit, each should have their own keycode (i.e. login) to enter into the storage unit?

Posted by Ujjwal Kaji Shrestha on 28 November 2010

Great analogy.

Wondering how database role factor into the equation.

Posted by mukeshknayak on 21 February 2011

This is brilliant and won't give me any confusion between users and login.

Posted by anitharoyan on 22 August 2011

No confusion between user and login good article

Posted by bony7babu on 26 March 2012

Excellent article.....

Posted by mayanktripathi4u on 5 April 2012

Awesome example....

Posted by lrosales on 8 May 2012

You got hit run. my man. great visual i got it.

Posted by harminderkaur26 on 16 August 2012

good job brian,since last 1 month i am trying to understand this concept ,but now it took me 5 min to get the clear idea about login and user:)

Posted by Dinesh Babu Verma on 29 August 2012

Very Nice..Cleared my Doubts...

Leave a Comment

Please register or log in to leave a comment.