Tip

Mapping user and login security

If you use SQL Server authenticated logins to connect to databases, the following scenario should sound familiar: you backup the database on the production server, restore it on the development/test server and the application doesn't work. What's wrong? You immediately check the users' node in the Enterprise Manager and notice that database users aren't mapped to respective logins. You next look at the list of logins on your development server and it's identical to that on your production server. How is it then that database users aren't mapped to logins?

SQL Server stores logins in sysxlogins table found only in master database. In SQL Server 7.0 and earlier logins were stored in syslogins table; in SQL Server 2000 syslogins is a view based on sysxlogins. When you create a login SQL Server automatically assigns a security identifier (SID) to that login. For example, the following statement creates a login "Joe" on my server and then retrieves its SID:

sp_ADDlogin @loginame = 'joe', @passwd = 'blah'
GO
SELECT name, sid FROM sysxlogins WHERE name = 'joe'

Result:

New login created.

name       sid           
----------- ----------------------------------- 
joe         0x1C8A8A5633855F4D9F2DAD14C51CEA05

At this point Joe could connect to server, but could not query any database on this server. Joe needs to be added to a database users' list before he can run any queries.

Within each database users are stored sysusers system table,

    Requires Free Membership to View

which also has a SID column. I could execute system procedure sp_adduser within pubs database to allow "Joe" any permissions within that database, as follows:
USE pubs
GO
sp_adduser 'Joe'

Result:

Granted database access to 'Joe'.

Now let's examine Joe's SID within pubs database's sysusers table:

SELECT name, SID FROM sysusers WHERE name = 'joe'

Result:

name        SID
----------- ------------------------------- 
Joe         0x1C8A8A5633855F4D9F2DAD14C51CEA05

Notice that SID is the same as it is in sysxlogins table in master database. That is precisely what needs to happen to map a login to a database user.

When you create logins on two different servers they're likely to have different SIDs, even if logins have identical names. Database users (actually sysusers table) are backed up on the production server and then restored on development server; however you probably don't restore your master databases nearly as often as you do user databases. Therefore user SIDs will be different from those of logins in sysxlogins table.

So how do we correct this issue? An easy solution immediately comes to mind: drop the database user and grant the login with the same name access to the database. That would work unless the user owns objects within the database. If the user indeed owns objects Enterprise Manager won't let you drop the user. You could of course allow updates on system tables and drop the user manually within Query Analyzer, as follows:

sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

USE pubs
GO
DELETE sysusers WHERE name = 'joe'
GO
sp_adduser 'joe'

Alternatively you could simply write an UPDATE statement to make sure that user SID and login SID are the same:

UPDATE sysusers
SET sid = b.sid
FROM sysusers a INNER JOIN master.dbo.sysxlogins b
ON a.name = b.name
AND a.name = 'joe'

Both of these solutions modify system tables directly, which is not a recommended practice. Fortunately Microsoft supplies a system procedure sp_change_users_login to accomplish the same thing without changing values in system tables directly. This procedure takes three parameters: @action, @usernamepattern and @loginname. The purpose of the last two parameters is easy to guess -- they contain the user name and login name, respectively. The @action parameter can have the following values: auto_fix, report and update_one. The first option, auto_fix will update the SID of a database user for which there is a login with the same name. For example, to correct Joe's SID we could run:

sp_change_users_login auto_fix, 'joe'

Report option will list each database user that isn't linked to a login. Finally update_one option will map a single login to a specific user. For example, we might wish to map "Joe" login to "Jimmy" user within pubs database, if so we would execute:

sp_change_users_login auto_fix, 'joe', 'jimmy'

Steps shown in this article might also come in handy if you use log shipping and you have to failover from primary to destination server.

One exciting feature of the upcoming SQL Server 2005 release is the concept of schemas. Schema is basically a collection of objects - tables, views, stored procedures, and so forth. In SQL Server 2000 the object owner couldn't be dropped until the object itself is dropped or object ownership is reassigned to another user (that could be accomplished by executing sp_changeobjectowner). So if Joe owns the authors' table then user Joe cannot be dropped from pubs database unless you change the authors table's owner to someone other than Joe or drop the authors' table. If your application calls authors table with a two or three part name ("joe.authors" or "pubs.joe.authors") then changing the object owner will require changes in the application.

In SQL Server 2005, Joe can have a schema named "Joe_Schema", which contains authors' table among other objects. If we need to drop user Joe you can simply re-assign the ownership of "Joe_schema" to another user, without having to change "authors" owner. This way the application that queries authors table can still refer to the table as pubs.joe_schema.authors and no application changes would be necessary.

This was first published in February 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.