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
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,
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.
sp_msforeachdb 'USE [?] DECLARE @OrphanedUsers TABLE ( UserName SYSNAME , UserSID VARBINARY(85) ) INSERT INTO @OrphanedUsers EXEC sp_change_users_Login ''report'' IF EXISTS ( SELECT * FROM @OrphanedUsers ) BEGIN DECLARE @FixMe NVARCHAR(MAX) 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) END '
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 ) ...
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:
- Document the proper procedure – if you’re impatient, just google “bcp out syslogins sql server” to get you started
- write a version of this for SQL Server 2000 – yes, I know it’s ancient, but some of us still have to support it…