1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3770992
  • 13
  • 1
  • 3

1 Answers1

1

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.

Troubleshoot Orphaned Users (SQL Server)

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.

simhumileco
  • 31,877
  • 16
  • 137
  • 115
sc_stang
  • 71
  • 3
  • Hi sc_stang, thanks for your reply! I have followed your instruction. The number of orphans was 2 (dbo and td). When I did step 2 (Autofix) for td it worked: The row for user 'td' will be fixed by updating its login link to a login already in existence. The number of orphaned users fixed by updating users was 1. The number of orphaned users fixed by adding new logins and then updating users was 0. – user3770992 Jun 26 '14 at 12:34
  • But when I did the same for user 'dbo' the following error occurred... Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 40 Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure. Am I doing something wrong? Am I missing something? – user3770992 Jun 26 '14 at 12:37
  • I believe running sp_changedbowner 'sa' on the db will add the sa back to the dbo which will fix the error you are seeing. sp_change_users_login will not work with dbo. – sc_stang Jun 26 '14 at 20:19