Restore SQL Database–Restoring security user settings

The issue:

I just restored a database backup file from somewhere. The users had a problem in a production environment and I want access to the same data to debug. The user (service account) created to access the DB is “Developer”. Why am I telling you this, well context can be useful sometimes Smile

So the restore is successful, yippee. I run my web app and it blows up with SQL login failed exceptions. Fail!

I read the exception carefully, and oh it mentions “Developer” failed to log in, aha, that should be a hint. I need to remap my current “Developer” service account on my local box (or wherever you restored your DB) to give it access rights to my DB.

How to fix it:

Expand the Security tab in SQL Server Management Studio. Right-click and select Properties. Note the DB I am pointing to with the green arrow (if your color-blind just look for the arrows Smile), it’s the one I just restored.

image

In the screen-shot below you will land on the General tab. Click/select the User Mapping option.

image

In the User Mapping window, it lists all the attached databases. The DB with the green arrow is not selected, so go ahead and check it as in the example below. Don’t forget, the row with the DB you just restored must be checked  but also make sure the row is highlighted, then look at the Database Roles section below. Make sure db_owner is checked (or whatever access privileges you desire).

image

Click OK to save the changes and you’re set!

Also if you would like the script for restoring the user role to the database rather than using the wizard:

USE [HydroChemFieldSystem_00031LCA]
GO
CREATE USER [Developer] FOR LOGIN [Developer]
GO
USE [HydroChemFieldSystem_00031LCA]
GO
ALTER ROLE [db_owner] ADD MEMBER [Developer]
GO

If you want a script to restore without having to use the wizard use the script below. Of course you will need to tweak the DB name “HydroChemFieldSystem” to suite your DB name and also the physical location from where the .bak file is restored from.

USE [master]
BACKUP LOG [HydroChemFieldSystem_00031LCA] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\HydroChemFieldSystem_00031LCA.bak' WITH NOFORMAT, NOINIT,  NAME = N'HydroChemFieldSystem_00031LCA', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5
RESTORE DATABASE [HydroChemFieldSystem_00031LCA] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Backup\HydroChemFieldSystem.bak' WITH  FILE = 1,  MOVE N'HydroChemFieldSystem' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\HydroChemFieldSystem_00031LCA.mdf',  MOVE N'HydroChemFieldSystem_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\HydroChemFieldSystem_00031LCA_log.ldf',  NOUNLOAD,  REPLACE,  STATS = 5

GO

 

Print | posted @ Wednesday, November 2, 2016 1:04 PM

Comments on this entry:

No comments posted yet.

Post A Comment
Title:
Name:
Email:
Comment:
Verification: