THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Bit Bucket (Greg Low): IDisposable

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Much ado about logins and SIDs

Some time back I posted about Logins and SIDs and I’ve had a number of people ask me for examples. So I’ve updated the post here.

A very common issue that's raised in the newsgroups relates to SQL Server logins that need to be moved between servers. When you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password.

The problem then comes when you restore a database from another server. You can't access it. If you try to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn't show it.

Let’s try an example: First I’ll create a database and a login and add the user to the database:

CREATE DATABASE LoginTest;

GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';

GO

USE LoginTest;

GO

CREATE USER GregTest FROM LOGIN GregTest;

GO

USE master;

GO

 

Next we’ll detach the database and drop and recreate the login:

EXEC sp_detach_db 'LoginTest';

GO

DROP LOGIN GregTest;

GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';

GO

 

If we reattach the database, we now have the situation where the new login has a different SID to the one that the user has in the database, even though the user has the same name. This is very much like what happens when you restore a database on another server and recreate the login there:

CREATE DATABASE LoginTest

ON (FILENAME = 'C:\SQLData\Data\LoginTest.mdf')

FOR ATTACH;

GO

 

If we try to use the login to access the database, it won’t work. If we try to create the login, it won’t work either:

USE LoginTest;

GO

CREATE USER GregTest FOR LOGIN GregTest;

GO

 

Msg 15023, Level 16, State 1, Line 1

User, group, or role 'GregTest' already exists in the current database.

 

The standard answer for this has been to use sp_change_users_login. It has an option to list any mismatched logins and database users ie: those with the same names but different SIDs.

 

EXEC sp_change_users_login 'Report';

GO

 

UserName    UserSID

----------------------------------------------

GregTest    0x5D5F9089AFE1D4428106DE1B52BE0DFC

It then has an option to "fix" it. The way it fixes it is to update the SID in the database user to match the login:

EXEC sp_change_users_login 'Update_One', 'GregTest', 'GregTest';

GO

 

In Service Pack 2 of SQL Server 2005, new syntax was introduced to deal with this:

ALTER USER GregTest WITH LOGIN = GregTest;

GO

 

The problem I see with this all is that it still just temporarily fixes the problem or at worst, propagates it to other servers. It's not the database SID that needs fixing, it's the Login's SID. If the Login's SID was correct, there wouldn't be a problem with copying the databases around. The most common scenario I see is the following:

1. A database is restored from another server (or a reinstalled server).

2. The logins that use the database need to be recreated.

I've gotten around this problem in the past by specifying the SID value when creating the login in TSQL. It is an optional parameter. If you provide the same value as on the other server, you don't have the problem. For example, instead of executing sp_change_users_login or ALTER USER above, we could have done the following:

USE LoginTest;

GO

SELECT sid FROM sysusers WHERE name = 'GregTest';

GO

 

sid

-----------------------------------

0x5D5F9089AFE1D4428106DE1B52BE0DFC

 

(1 row(s) affected)

What we could then have done was:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',

  SID = 0x5D5F9089AFE1D4428106DE1B52BE0DFC;

GO

 

The upside of this is that it’s a permanent fix. Next time you restore the database, you won’t have to fix it again.

Because this is such a common issue, I’d really like to see it directly supported in T-SQL, such as:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',

  SID FROM DATABASE LoginTest;

GO

 

This would avoid the problem in the first place and then give you a database you could copy around as needed. If you think that sounds interesting, vote for it here:

 https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=269442

Published Monday, February 02, 2009 5:29 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ben Thul said:

I just had this problem the other day in moving databases from one server to another.  I solved it like so:

<code>

select

'create login [' + dp.name + '] password = '''', sid=' +

master.dbo.fn_varbintohexstr(dp.sid)

from

sys.database_principals dp

left join

sys.server_principals sp

on

dp.sid = sp.sid

where

dp.principal_id > 4 and

sp.name is null and

dp.type_desc = 'SQL_USER'

</code>

February 2, 2009 12:43 PM
 

James Luetkehoelter said:

Actually Greg, I think I disagree with you - I don't like the WITH SID option. I would rather see some sort of automated sp_change_users_login fire when a database is attach and attempt to fix orphan users, presenting you with a dialog showing its suggestions.

My worry here is security. Not the kiddy-hacker slammer type security, the real stuff. Now if I were to actually hack into a server where I had these rights, couldn't I use something like this to create a sysadmin login on another server? I'm still mentally chewing it, but it still worries me.

Incidentally, the same SID issue occurs when you move Windows users between un-trusted domains.

Still thinking on this though...

February 2, 2009 4:02 PM
 

Greg Low said:

Hi James,

What about the scenario where you take databases from two servers with the same username/password but different SIDs and restore them onto one server? One would have to lose.

Regards,

Greg

February 2, 2009 4:13 PM
 

James Luetkehoelter said:

Well, that would be where an interface would come in - to let you verify or change names...I'm not sure about this, still chewing the security implications. Definitely if going between untrusted domains, this would be an issue. But, if you would be doing that, you should probably be doing it with a user bound to a SSL cert.

February 3, 2009 1:43 PM
 

Ken H. said:

Or, rather than using these commands, one could just use the SP created by MS:

http://support.microsoft.com/kb/918992/

EXEC sp_help_revlogin

February 4, 2009 8:22 AM
 

Greg Low said:

Agreed Ken. The only problem I have is that I often don't want to create database objects, so I've built scripts that don't require that. At least in recent versions we have a built in way to convert binary values to hex. That makes it easier.

February 4, 2009 4:40 PM
 

Wile1one said:

Complexity of this kind - in crash sensitive area of login security can be hard to manage...  -  Bens solution is cool in that it appends the system permissions inside your database...   but automatically appending system permissions is also an "as at " situation.  It can have undesired consequences...  such as reinstating a system level permission to someone who has been downgraded in your database...  

James Solution in binding to an ssl cert is fine if the certs reside across multiple servers...

I have always carried a redundant table in my databases which hold users and passwords and permissions.  I propogate permissions FROM this table..   and I initiate a trigger to a log table of people in my table who do not appear to have permissions on the server...  

One can do many things with that log table... such as email to the administrator in small applications,   or initiate an admin role SP to add the user in question...  

The overhead cost of my redundant security table is negligable...  and it has bailed me out many times...    

February 9, 2009 7:16 AM
 

Sync-ing SQL logins and database user accounts across servers with SIDs « LincolnBlog.net said:

April 22, 2014 10:18 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement