Sql-server – Restore a database backup on another server without sysadmin account

restoresql server

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.

Best Answer

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.

Related Solutions

Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

to relink the login to the correct id you will have to run (I assume that the login is appuser, and the databas user appuser):

alter user appuser with login = appuser ;

From BOL:

To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema. Requires CONTROL permission on the database to remap a user to a login

As you explained that you have the db_owner role on the database, it should work.

Sql-server – After moving database (backup, restore), I have to re-add user

This is the difference between logins and users and how they relate to each other:

Logins - Instance level principals that allow an entity to connect to the SQL Server instance. They do not, by their nature, grant any access to databases on the instance. The exception to this is a login with sysadmin rights can use a database because they are sysadmin, but because of sysadmin level permissions.
Users - Database level principals that allow an entity to connect to a SQL Server database. Users are associated with logins via SIDs, creating a relationship between the two and allowing a login to connect to the instance and then use the associated user to connect to the database.

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected. You can fix this with the following SQL:

ALTER USER [foo] WITH LOGIN=[foo]

You can use the following query in the context of your database to check for orphans:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,'Orhphaned!') [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in ('S','U','G')
    and dp.principal_id >4
order by sp.name

Best Answer

Related Solutions

Sql-server – Maintaining or Re-Gaining access after SQL Server database restore without SYSADMIN rights

Sql-server – After moving database (backup, restore), I have to re-add user

Related Question