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.