Logins vs Users
20December 3, 2012 by Kenneth Fisher
Logins are not Users. It’s a pretty easy concept but one that seems to give a lot of people problems. So let’s explore it a little bit.
Logins, sql and windows, are stored in sys.server_principals and are server specific. They are assigned server level permissions and can be added to server level roles. Probably the most important permission a server principal can have is CONNECT. This is connect to the server only! If I create a login and give it the CONNECT permission then that login can connect to the instance. Important concept here! This does not mean it has any ability to connect to or even see the databases on that server. Internally each entry into sys.server_principals is assigned a SID. (Security IDentifier) In the case of windows logins/groups the SID matches the windows SID. SQL logins have an SID assigned to them by the instance.
Users on the other hand are stored in sys.database_principals and, as the name signifies, are database specific. They are assigned database level permissions and can be added to database level roles. Again in my opinion the most important permission a database principal can have is CONNECT. This time though it is connect to the database only and implies no permissions at the server level, or in fact at the database level other than the ability to connect to it. “USE dbname” for example. Sys.database_principals also has a SID column. In the case of windows logins/groups and SQL logins this SID is the foreign key back to the sys.server_principals catalog view. For other principals this value is NULL.
FYI CONNECT at both a server and database level is typically added automatically when the login or user is created. You will probably never need to manually add the permission.
Ok so far I’ve very briefly explained what logins and users are and what makes them different. There are two subjects that come up fairly frequently as a result of the difference between the two.
First: Names! A login is not the same as a user so there is absolutely no requirement for the login name and the user name be the same! (sorry for yelling, it’s a pet peeve of mine) I frequently get users (and other DBAs) coming to me confused because the login name is Domian\Abcd but the user name is Abcd. It’s probably easier if you name them the same thing but while a windows login has to be named the same in SQL as it is in Windows at a login level, the user name could be completely different. As for SQL logins, your SQL login could be SleepingBeauty and your user could be PrinceCharming and the system itself won’t care in the slightest. You will probably confuse your users down the line but things will work just fine.
Second: Orphaned users. Frequently when a DBA is asked to restore a database from production down to test or dev, they get the restore done correctly, but the users can’t connect. It still happens to me about 50% of the time even now. What has happened is the users SID doesn’t match any of the SIDs in sys.server_principals. Historically you would use sp_change_users_login to find and fix orphaned users but since it’s been depreciated I’ll let everyone go to BOL if they are interested. The only method I know now to find orphaned users is a fairly simple query:
SELECT * FROM sys.database_principals WHERE sid NOT IN (SELECT sid FROM sys.server_principals) AND sid IS NOT NULL AND type <> 'R' AND sid <> 0x00
Once you have the name of an orphaned user use the following code to fix it.
ALTER USER username WITH LOGIN = logintoassign
You will need to make sure the login already exists before you run the ALTER USER however. And in fact if you have an orphaned user that is a windows login/group that you moved from another instance all you have to do is create the login on the new instance. Assuming the two instances are on the same domain the SID for the newly created login will be the same as the SID for the user and no additional work will be needed.
Category: DMV, Microsoft SQL Server, Security, SQLServerPedia Syndication | Tags: database permissions, DMV, microsoft sql server, security, server permissions
20 thoughts on “Logins vs Users”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
SQL Studies 
I found years ago a good analogy to understand the difference.
SQL Server is like an apartment with several rooms. When your dba assigns you a login, he’s giving you the key to get to the floor, but you cannot leave your assigned room (master database; or your default room-database, if you’ve got one).
Then the dba tells you: you have permission to enter the following rooms-databases, and then you’ve just become an user of them.
In each room you can use a different name, so the login and the user need not be the same (as you can be Richard in some formal places and also be called familiarly Rick in others).
Also, in certain rooms you can touch and move objects, and in others you can look but better don’t touch, boy (Bruce Springsteen borrowed)
Very nice analogy. I’ve always used the file cabinet analogy, but for this I think I like yours better.
I like my analogy even better: your SQL login is equivalent to your front desk security pass, while the database user account is equivalent to the office security. (Where I work, the lobby security won’t let you through the turnstile unless you have a security badge. Once you get through the turnstile, you don’t have permission to run willy-nilly throughout the building, but instead your badge will get you through one or more office doors.)
“First: Names! A login is not the same as a user so there is absolutely requirement for the the login name and the user name be the same!”
For clarity – I would suggest filling in the missing word 🙂
I’m not sure what word is missing? (And I do want this to be as clear as possible 🙂 )
User names do not have to be the same as login names.
Hence – you’re missing at least a ‘not’ for that sentence to be exact – as it stands now it could mean either way.
As a non-English native speaker, I wouldn’t go about correcting spelling/grammatical errors as I make plenty myself, but the lack of the ‘not’ could easily misrepresent your point.
Oops, found it. Unfortunately while I am a native speaker, grammar and spelling have always been issues for me. Honestly at times I ask my 8year old son for help with spelling. I’ll fix it.
I really appreciate posts like this. As an accidental DBA, I find every subject in SQL so deep, it is easy to miss the very basics as you run around troubleshooting. Your explaination was complete without falling off the deep-end. I was smarter by the end of the second paragraph.
Thanks! Nicest compliment I’ve had in a long time. Honestly you are my target audience, accidental and beginning DBAs that is. I’m reasonably good at SQL, but I’m definitely no Brent Ozar or Jonathan Kehayias. I’m just good enough to write about the basics for people who might be interested in them. FYI If you want a cool SSMS setting take a look at this post. At Startup SSMS setting
They’re not stored in the sys.server_principals. It’s just a DMV, that presents them.
Absolutely true. They are actually stored in some system tables with sys.server_principals as a view. I probably should have said “logins can be found in sys.server_principals” to be more correct. Please do remember though that Microsoft is heavily discouraging the use of system tables in code (I’m not even sure if you can access them at all) and all references to logins and users should be done using the views sys.server_principals and sys.database_principals.
as a dba how i differentiate login and user
I think login is the credential who give us access for server os or to connect a server (not database) and users are credential which give us access to database.
please correct if i m wrong
You are correct. A login is at the server, or to be more precise, the instance level. The user is at the database level.
[…] First here is a list of users/database principals (read about users/database principals vs logins/server principals here). […]
[…] In all cases remember that among other things server principals, server permissions, connected databases and some server settings are stored in master. Any changes […]
[…] with a user. Only a login can have server level permissions such as sysadmin. I discussed this here if you are […]
[…] https://sqlstudies.com/2012/12/03/logins-vs-users/ […]
[…] the most common problems I see with this is forgetting to create an associated database principal (user) for the default database. This will make it impossible to connect using that login without […]
[…] 11. Logins vs Users | SQL Studies […]
[…] Logins vs Users | SQL Studies […]