THE SQL Server Blog Spot on the Web

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

Greg Low (The Bit Bucket: IDisposable)

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

Much ado about logins and SIDs

This blog has moved! You can find this content at the following new location:

http://greglow.com/index.php/2009/02/03/much-ado-about-logins-and-sids/

Published Monday, February 2, 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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement