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?
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
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.
I hope this works for your case.