How can I change a user created without login to a user with login

Question

How can I change a user created without login to a user with login

TC-416047

Ten Centuries

Points: 1312
More actions
August 15, 2013 at 2:56 am

#282088

We lost a raid controller (supposedly) on a brand new database server Wednesday at about 3:45pm. Just before our 4:00 backup. I'm trying to attach our main database and get it running at that closest point.
We have reinstalled SQL Server 2008 R2 (long story talk to DELL)
I am able to attach the 3:45 mdf and ldf files but the users are without logins.
How can I get the db users to have a login and password again?
Before 6:00am this morning ..... central time USA.
its 3:55am now.
This is an emergency and any help is sincerely appreciated.
tia,
Todd

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33508 More actions · Answer 1

If possible, restore the [master] database from the last good backup (or more and very(!) risky: use the mdf/ldf files). When you have restored the master database, all logins are available with the correct UID's. Therefor the users within the user-database(s) will be automaticly (re-)connected to the corresponding logins.

You could also recreate the logins and then use the "sp_change_user_logins" command to reconnect the users with the logins. But with this method you won't be able to get the old passwords of the logins. You have to set new passwords and optionally let the users change them on first login.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

TC-416047 Ten Centuries Points: 1312 More actions · Answer 2

First thanks for the response! Very much appreciated.

I only have the mdf and ldf files for the master db.

how should/can I restore them?

TC-416047 Ten Centuries Points: 1312 More actions · Answer 3

Since I already attached the production database aren't the UIDs already changed?

I'm uncertain about the order of things to do.

HanShi SSC-Dedicated Points: 33508 More actions · Answer 4

TC-416047 (8/15/2013)
Since I already attached the production database aren't the UIDs already changed?
I'm uncertain about the order of things to do.

The UID of the user is stored in the userdatabase. The UID of the login is stored in the [master] database. When these UID's match then the user is connected to the login.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

TC-416047 Ten Centuries Points: 1312 More actions · Answer 5

i have never had to restore the master db.

Is the process the same as restoring any other database?

HanShi SSC-Dedicated Points: 33508 More actions · Answer 6

TC-416047 (8/15/2013)
First thanks for the response! Very much appreciated.
I only have the mdf and ldf files for the master db.
how should/can I restore them?

If you have a backup available, it would provide a better and far more safer solution. See http://technet.microsoft.com/en-us/library/ms190679.aspx for a step-by-step.

Without a proper backup we are going on a very risky path here, so take good care. This solution could brake your whole SQL instance!

If the folderpath and the serviceaccounts of the new instance installation matches the broken one, you could try shutting the SQL instance down. Rename the existing "master.mdf" and "master.ldf" file. Copy the original files to this location and try to start the SQL Services. Keep a close look if the services are able to start (and stay up) en also watch entries in the Windows Event log.

If the services stop you have to revert to the previous situation by renaming the files again.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

TC-416047 Ten Centuries Points: 1312 More actions · Answer 7

I have a backup of the master from the 13th at 8:00pm

If I use that what would the impact be on my data?

HanShi SSC-Dedicated Points: 33508 More actions · Answer 8

The master database contains all information about the instance settings, logins, userdatabase definitions, etc. Any changes made after the available backup time (like a password change, adding/removing a database) have to be applied again.

In many environments these are actions that are not frequently executed. You probably have a rather up-to-date situation after the restore.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

TC-416047 Ten Centuries Points: 1312 More actions · Answer 9

OK this was good but one more issue:

When I change to single user mode and stop all sql services i cannot login. It says only one administrator can login.

So i go back to the service in the sql configuration manager and try to change the user from network services to sa. My sa password does not work when trying to login.

I have 2 minutes to go. its going to be a rough day.

No sleep and screaming users.

Any thoughts or ideas?

HanShi SSC-Dedicated Points: 33508 More actions · Answer 10

Make sure that all SQL related services are stopped. This also includes the SQL Browser and possibly a third party backup application. Also make sure that no applications connect to the SQL instance.

You have to be the only one connecting (from a seperate command prompt) to the instance running in single user mode.

So:

- stop all services

- open a command prompt and start SQL in single user mode

- open another command prompt and login to the instance

- execute the restore command

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

TC-416047 Ten Centuries Points: 1312 More actions · Answer 11

HanSHi,

I want to thank you for your help and pointing me in the right direction.

All worked out well. I learned a lot through this event.

Its back to zero based thinking and making sure this never happens again.

Thank you.

Todd