Because I’m already ridiculously late, I have a short one. This is about orphaned users — you know, when you restore a database and its users aren’t mapped to the server logins that they should be or used to be.
The typical solution is
sp_change_users_login with the
update_one option. But guess what? Yep, that’s deprecated. By the way, did you know that it also has a
report option? Apparently that’s got some bugs…ish? Try it sometime and see — compare the output of
sys.sp_helpuser where the ‘LoginName’ column is null, with
sp_change_users_login 'report'. Preferably on a DB you’ve restored from another server. 😉
So what’s the correct solution?
ALTER USER [theUser] WITH LOGIN = [theLogin]. Simple, no? Let’s get more general. Could we come up with a half-decent way do apply this kind of fix dynamically? Well sure, the nice folks at DBATools have already solved that problem. And that’s great, really. But just in case that doesn’t work… ^_^
One of the many things I love about SQL Prompt is the right-click option to “Script as INSERT” (from the results grid). This is a quick & easy way to built a temp-table for the results of an exec statement so you can do the ol’
insert #tmp exec sys.sp_blah ! Then we can query the list of DB users for the null
LoginNames and write a little set of queries to fix them! Sound good?
PS: Coincidentally, today’s (Thursday) SQL Server Central newsletter featured a very similar post by a gentleman over at Madeira Data. Go check it out, it’s another great solution to this problem! And while you’re at it, get the SQL Server Radio podcast (created by a couple guys from the same company) – it’s a terrific addition to your iTunes library.