Recent Articles:

The Week in Awesome – Plugins, Cars, Food, and France


awesomeThis week in awesome:

Denny Cherry – our own @mrdenny – has created a Password Vault WordPress plugin. That looks seriously cool.

Google has created a prototype of a totally self-driving car. It looks like a toy, but feels like the future.

As long as we’re talking about cars, go look at The Oatmeal’s great new comic “What it’s like to own a Tesla Model S“, if you don’t mind a bit of naughty language. Which I don’t. Warning: Will make you want to buy a Tesla Model S.

Sean at PluralsightLast week, Sean and I flew to Chicago to film a career course with @pluralsight. We are, shall we say, extremely excited about it.

@ScriptingWife says “New #PowerShell User Group locations I am working with to get a group started, Orange County CA, Orlando FL, New Jersey, Austin TX, Denver”  I’ll  be looking out for that.

Here’s an article that makes the wild and crazy claim that coding is not easy. I’m actually down with that, as I am with the “Recursive Recipe for Learning to Program” given therein. (Via @ShawnHooper.)

Me: All I want is to be able to consistently copy-paste from remote. Is that too much to ask, lord?
@markvsql: Lord, won’t you get me some Copy and Paste. I have to keep setting it up, it’s just such a waste…
Me: Prove that you love me, and fix this, in haste…oh lord, won’t you get me, some copy and paste.
[You really have to know your Janis Joplin for this one.]

In Twitter recipe news: “Fry the bacon in the waffle iron, then make a waffle. It’s amazing.”  @dmmaxwell.

@spacechelle let us know that there’s going to be a Disney-Star-Wars-half-marathon-thingy in 2015. Count me in.

Success convinces us that we are doing things the right way. There is nothing quite as effective, when it comes to shutting down alternative viewpoints, as being convinced you are right.
- Pixar founder Ed Catmull (via wilwheaton)

@billinkcSubmitting a Connect item to send mild to strong electrical currents to people storing concatenated lists in columns
[I'd support this.]

Finally, tonight is the season 5 ender for our weekly webshow, DBAs@Midnight. Come see us live at http://webshow.MidnightDBA.com at 11pm Central time, because we’re next live on the air sometime in, oh, August maybe. (Now seemed like a good time to end the season, as we’re about to spend a few weeks in France. I know you’ll miss me, but we’ll always have Twitter and all the “from the vault” posts I scheduled to appear here!)

Happy days,
Jen
http://www.MidnightDBA.com/Jen

P.S. Some nonzero percentage of you readers nodded approvingly at the Oxford comma in the title. I nod approvingly, right back at you.

Applied SQL: Orphan a user, fix orphaned users (sp_FixOrphanedUsers)

olivertwistEver heard of orphaned users? It’s a database user account, disconnected from its associated login. You typically get this when you take a database backup, and restore it to a different server.

Microsoft provides us with the ever helpful sp_change_users_login to find and repair orphaned users, but even so, it’s kind of a PITA* manual operation. To simplify and provide for automation, I’ve put together the brand-spanking-new sp_FixOrphanedUsers.

First, let’s orphan some users

If you’d like to test this, here is a repro scenario that creates an orphaned user on a single instance. It orphans a user by doing the following:

  1. Create a login “OliverTwist”**.
  2. Create a database.
  3. Create a user in that database from the “OliverTwist” login.
  4. Back up the database, then drop it.
  5. Drop the login.
  6. Restore the database.

I will say that steps 4 and 6 aren’t strictly necessary; just dropping the login will orphan the user. But I’d like to drill the idea of orphans being associated with restores, because that’s the most common scenario in real life.

Important: ALWAYS review code before you run it, even on a test instance. 

Now let’s resolve a single orphaned user

When you have an orphaned user, it’s really easy to detect and fix them. In our case, we’d find the orphan by running

EXEC OrphanedUserDemo..sp_change_users_login 'Report';

And then, we could fix that orphan by first creating the missing login, and then running

EXEC OrphanedUserDemo..sp_change_users_login 'AUTO_FIX', 'OliverTwist';

(Or, if we want the missing login to be created automatically, then “EXEC OrphanedUserDemo..sp_change_users_login ‘AUTO_FIX’, ‘OliverTwist’, NULL, ‘NewPasswordOMG!!!’;“)

But this is a manual process, and therefore slow and tedious and not entirely worth our time.

Let’s resolve many orphaned users

Create the stored procedure sp_FixOrphanedUsers in master, and run it as [DBName]..sp_FixOrphanedUsers, and it fixes all orphaned users in that database, if it can do so automatically.

Better still, you could run sp_FixOrphanedUsersAllDB (included) to run this process against all databases on the SQL Server instance.

If you have a regular refresh process – like restoring a set of databases to UAT – you could create a second step in the job to run sp_FixOrphanedUsersAllDB, and greatly simplify your life.

Limitations and caveats

Of COURSE there are limitations, silly reader!

  • Don’t trust code you download from the web. ALWAYS review code before you run it, even on a test instance.
  • This is the first iteration of the procedure(s). Doubtless, you and other readers will have helpful suggestions on how to improve the thing.
  • In this iteration, the SP doesn’t have the ability to specify a password to auto-create missing logins. I think this is a really bad practice. (“Oh, this server doesn’t have a login for ‘GodlyAdminGuy’? Let’s create on with ‘P@ssw0rd!’ for the default!” Yeah, no.)
  • And other things I haven’t yet thought of, but will doubtless be pointed out to me enthusiastically and repeatedly. Feel free, but be cool.

Downloads

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

*PITA = “Pain In The Analytics”, so far as you know.

** GET IT??

Video: Designing Stored Procedure Solutions

okc2014My new session for 2014, “Designing Stored Procedure Solutions”, is now up on the Sessions page: abstract, video, and code!

This month, I gave this session at the Oklahoma City SQL Server User Group‘s 5 year anniversary, and at the Alaska SQL User Group, to thousands and thousands* of attendees and wild** applause. I’m looking forward to giving this session again soon!

Happy days,
Jen McCown

*Dozens

**Polite

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/