Restoring Orphaned SQL Server 2008 Users

We keep moving databases across servers and reinstalling SQL Server at my office. Almost everything transitions perfectly except SQL Server Users. Unless the user already exists in the Master Security table, the database user is left orphaned inside the database without the ability to perform any useful function. Like you right now, but we're gonna change that.

Here is an example, note how there is a User Name but no Login Name.

User Name but no Login Name

Step 1: Recreate the User in Master
First, if they don't already exist, is to recreate the user in the master database by browsing to the root level Security folder in SQL Server Management Studio, choose Logins and right-click create user.

You tried to check the database database under the "User Mapping" page didn't you?

Create failed for User 'UserName'.

That was a good idea hotshot, but it ain't gonna work for you. Just create the user and Ok your way out of there. Time for Step 2.

Step 2: Remap User to Database
We need to run a system stored procedure I found on MSDN, it is pretty straightforward, unfortunately it must be performed for each database.
NOTE: There's got to be a better way, and supposedly this method is deprecated, but this is the only thing I could get to work in my instance, so if you got something better please share.


USE DATABASENAME;
GO
EXEC sp_change_users_login 'Auto_Fix', 'UserName', NULL, 'UserPassword';
GO

Where:
DATABASENAME = The name of your database
UserName = SQL Server Login to repair
UserPassword = SQL Server Login's password to repair

Full documentation on MSDN for sp_change_users_login
http://msdn.microsoft.com/en-us/library/ms174378.aspx

Comments

Attachment Reminder - and more for MS Outlook

I just did it again. We don't like to admit it, but we all have. You write a long letter describing the attachment, press send and then 10 seconds later remember you didn't actually attach the message. I finally decided to do something about it. Turns out it isn't too hard. Chiefly because Jimmy Peña at Code for Excel and Outlook already did all the hard work of writing up an excellent MS Outlook Etiquette Check Macro that does all the dirty work for you. What's left for you to do? In MS Outlook go to Tools > Macros > Visual Basic Editor Under the Project Panel (far left) Browse to Project1 > Microsoft Office Outlook Objects > ThisOutlookSession Double-click ThisOutlookSesson to Open (if you haven't been here before this will be a big blank canvas) Visit Code for Excel and Outlook Etiquette Check Code and select "Copy to Clipboard" at the top of the code. Or you can also copy from the code I've modified below if you prefer....

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects. It looks like it'll be as easy Apache finally. That is until you try to redirect a querystring. Then everything bombs. Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect. And How. In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content. Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console. In the HTTP Redirect Dialog: Check Redirect requests to this destination Enter your new path ending with $V$Q. e.g. http://mydomain.com$V$Q Counter-intuitively check Redirect all request to exact destination (instead of relative destination) Choose the appropriate Status Code (Permanent or Temp...

ArcGIS One-to-Many Labeling

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one. We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships. The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this? I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine. We're going to do a database query inside a label expression, loop through the results and output the entire list to label. For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names. 1.) Open ArcGIS and add your spatial layer with the unique identifier shared with...

oneidía

Search This Blog