user3770992 user3770992 - 3 years ago 141
SQL Question

Restore database rights on restored database SQL Server 2012

We are restoring a SQL Server 2008 backup database on a SQL Server 2012 database server. The restoring goes well, the files are created and the database is online.

But unfortunately, we do not have any rights on the database, other than read.
In the past, if I could remember right, we could delete roles/schemas from the restored database and so, restore other rights to the database. This unfortunately will not work now...

Can anyone help us or point us to a site where we could set the security back to the restored database?

Answer Source

I have not tried this when restoring to a different version of sql server but it sounds like you have orphaned users and/or missing users.

Note: I do this in a dynamic sp in the master db that goes through all the dbs i restore and fix the users connections for a new development environment.

The steps I do are

  1. EXEC [@dbname].dbo.sp_change_users_login 'Report' -- Find Orphaned
  2. EXEC [@dbname].dbo.sp_change_users_login 'AUTO_FIX', @UserName -- Fix Orphaned if in instance

If the report returns users the instance does not have I also create the user. This is only for a development environment, so I would adjust this accordingly.

  1. CREATE LOGIN @UserName WITH Password = '@Password', CHECK_POLICY = OFF -- Create the missing user
  2. EXEC [@dbname].dbo.sp_change_users_login 'AUTO_FIX', '@UserName', NULL, '@Password' -- Fix Orphaned to user that was just created.

I hope this works for your case.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download