SQL Server Logins and Users

Gleamed from akadia.com

SQL Server Logins and Users
Overview
Although the terms login and user are often used interchangeably, they are very different.

  • A login is used for user authentication
  • A database user account is used for database access and permissions validation.

    Logins are associated to users by the security identifier (SID).
    A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called “authentication”. This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server. The single exception to this situation is when the database contains the “guest” user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.

    When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having “orphaned users.”

    Troubleshooting Orphaned Users
    When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.


    Use master
    sp_addlogin ‘test’, ‘password’, ‘Northwind’

    SELECT sid FROM dbo.sysxlogins WHERE name = ‘test’
    0xE5EFF2DB1688C246855B013148882E75


    Grant access to the user you just created
    Use Northwind
    sp_grantdbaccess ‘test’
    SELECT sid FROM dbo.sysusers WHERE name = ‘test’

    0xE5EFF2DB1688C246855B013148882E75

    As you can see, both SID’s are identical.

    Backup the database

    Use master
    BACKUP DATABASE Northwind
    TO DISK = ‘C:\Northwind.bak’

    Copy the Backupfile to another Maschine and SQL Server and restore it as follows:

    RESTORE FILELISTONLY
    FROM DISK = ‘C:\Users\Zahn\Work\Northwind.bak’

    Northwind
    Northwind_log

    RESTORE DATABASE TestDB
    FROM DISK = ‘C:\Users\Zahn\Work\Northwind.bak’
    WITH
     MOVE ‘Northwind’ TO ‘D:\DataMSSQL\Data\northwnd.mdf’,
     MOVE ‘Northwind_log’ TO ‘D:\DataMSSQL\Data\northwnd.ldf’

    The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.

    Check the SID's

    Use master
    SELECT sid FROM dbo.sysxlogins WHERE name = ‘test’

    0x39EE98D37EAC2243B7833705EC1C60E3

    Use TestDB
    SELECT sid FROM dbo.sysusers WHERE name =’test’

    0xE5EFF2DB1688C246855B013148882E75

    Now, to detect orphaned users, run this code

    Use TestDB
    sp_change_users_login ‘report’

    test 0xE5EFF2DB1688C246855B013148882E75

    The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the TestDB database, and the sysxlogins system table in the master database.

    Resolve Orphaned Users

    Use TestDB
    sp_change_users_login ‘update_one’, ‘test’, ‘test’

    SELECT sid FROM dbo.sysusers WHERE name = ‘test’
    0x39EE98D37EAC2243B7833705EC1C60E3

    use master
    SELECT sid FROM dbo.sysxlogins WHERE name =’test’

    0x39EE98D37EAC2243B7833705EC1C60E3

    This relinks the server login “test” with the the TestDB database user “test”.
    The sp_change_users_login stored procedure can also perform an update of all orphaned users with the “auto_fix” parameter but this is not recommended because SQL Server attempts to match logins and users by name.
    For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.

    Advertisements
  • 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