Restore a database backup on another server without sysadmin account

Question

I need to restore my own database backup on another server.

On both servers I have the same user account name with limited privileges (eg WebUser).

On the target server I can't use a sysadmin account, so how can I restore my backup and give access to limited user account WebUser?

Of course I can't use SQL Server Management Studio but only code in my app.

I've tried with an account with this server role

[serveradmin] 
[diskadmin] 
[dbcreator] 

I can restore the backup but can't access it nor give permissions on the database.

• Kin Shah: Ask your DBA to do the restore. There might be valid reason why the permissions are not given to you.

Answer 1

I think your main issue is that after restoring the database to your new target user, that your account "WebUser" is effectively orphaned.

You should have your DBA change the SID for WebUser (and any other user that matters) to be the same as in your source server.

Note that this will require dropping the user and re-creating it in the target server. And any other databases will need to be fixed as well.

But this will allow you to restore the database from the source server, to the target server and not have to worry about permissions when you get done.

Answer 2

Use any login with [securityadmin] server role on target to create your user [WebUser] on restored db and assign [db_datareader] role on same.

• Andriy M: securityadmin doesn't appear among the roles available to the OP.