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
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>
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…
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
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.
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
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.
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…