Restoring into a different environment? Security problem? Orphaned Users

Oh, man, it’s been a fun ten days or so. Mostly not of my doing, thankfully. However, one thing that was definitely down to me was a minor little hiccup with SQL Server security.

Y’see, when you RESTORE a database into an environment other than that from which you created the BACKUP, chances are you’re going to have some security issues. In this case, we found that the UAT version of our applications was throwing the following error message:

Cannot open database "foo" requested by the login. The login failed. Login failed for user 'bar'.

This is down to the way our non-production environments had been built – in a hurry, and without doing the SQL Logins necessarily in The Right Way.

So now, every time I rebuild this environment from Production (quarterly), I have to remember to go through checking for “orphaned users” – users which exist in the SQL Server database I’ve just restored, but not necessarily in the server itself. Or (more likely in my current situation) do exist at the server level, but with the wrong SID (security identifier – a nasty-looking string of hex garbage).

For extra fun, I was also fighting against inconsistent collations. A pox on non-existent standards. (Is that even possible?)

Identifying Orphaned Users

This is obviously enough of a routine problem that MS has kindly provided a stored procedure to do the job for us. Yay.

sp_change_users_login is your friend. Run this, with the @Action parameter (first) set to ‘report’, and you get a list of user IDs that exist within the current database but don’t exist with the same SID. Hurrah.

Capturing that output

DECLARE @OrphanedUsers TABLE (UserName sysname, UserSID varbinary(85))
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'

Processing the list

Given that, in my case, I’m only interested in fixing those users who already exist in my environment, I can identify them by:

SELECT * FROM @OrphanedUsers
WHERE UserName IN (
    SELECT name FROM master.dbo.syslogins

Fixing one orphaned user

The sp_change_users_login SP takes, as one option for the @Action parameter “Update_One“. This is what I’ll use to fix each individual item identified in the table above. However, as there are several users in the database for which I wish to do this, and I’m lazy and don’t want to have to type too much, I’ll do this:

DECLARE @FixUsers varchar(max)
SELECT @FixUsers = ''
SELECT @FixUsers = @FixUsers + 'exec sp_change_users_login ''Update_one'', ''' + UserName + ''', ''' + UserName + ''' '
FROM @OrphanedUsers
WHERE UserName IN (
    SELECT name FROM master.dbo.syslogins

That gives me a variable, @FixUsers, that contains the right series of EXECUTE statements to fix those users that I need to fix. I can then lob this big lot of EXEC statements at an, erm, EXEC statement:

EXEC (@FixUsers)

And the job’s a good ‘un.

Doing this for all databases

In my case, though, this isn’t sufficient. I have multiple databases in this environment (30-odd), several of which suffer this embarrassing problem. Rather than go through each DB manually running this script, why not make use of the undocumented (and therefore highly dangerous) sp_MSforeachDB stored procedure? This does what it says on the tin – runs a command once for each database on the system, substituting the name of the database wherever you’ve got a question mark (?) in your script.

Given that I’m therefore having to build up dynamic SQL, the apostrophes can get a little out of hand. However, here’s the script I ended up writing and running, run through Red-Gate‘s “format-SQL” site.

'USE [?]
DECLARE @OrphanedUsers TABLE
      UserName SYSNAME ,
      UserSID VARBINARY(85)
INSERT  INTO @OrphanedUsers
        EXEC sp_change_users_Login ''report''
            FROM    @OrphanedUsers ) 
        SELECT  @FixMe = ''''
        SELECT  @FixMe = @FixMe + ''
    exec sp_change_users_login ''''update_one'''', '''''' + UserName + '''''' ,
                '''''' + UserName + '''''' ''
        FROM    @OrphanedUsers
        WHERE   UserName IN (
                SELECT  name 
                FROM    master.dbo.syslogins )
        PRINT @FixMe
        --EXEC (@FixMe)

Uncomment the EXEC (@FixMe) line when you’re ready.

Now, I had collation issues, so the WHERE clause of that innermost SELECT statement had to be

        WHERE   UserName COLLATE SQL_Latin1_General_CP1_CI_AS IN (
                SELECT  name COLLATE SQL_Latin1_General_CP1_CI_AS
                FROM    master.dbo.syslogins )

Deep joy.

Caveat – sp_MSforeachDB

Now, a word of warning. sp_MSforeachDB is an undocumented stored procedure from Microsoft. Because it is undocumented, it is unsupported, and may not work, and may be changed (or dropped) at any point. You’re best off using something else, such as this more reliable and flexible replacement for sp_MSforeachDB from Aaron Bertrand (blog|twitter).

Still to do

Two things outstanding:

  1. Document the proper procedure – if you’re impatient, just google “bcp out syslogins sql server” to get you started

  2. write a version of this for SQL Server 2000 – yes, I know it’s ancient, but some of us still have to support it…
This entry was posted in SQLServerPedia Syndication. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your 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