Backup permission

Dear All,
Sorry this is probably a bit of an old chestnut, but I couldn't see it in
recent posts...
I understand that when you backup a db using SQL database backup, you need
to reapply the permissions when it is restored. (I notice that I have lost
the permissions on the backup).
2 questions:
- Is there a way to backup and keep the permissions?, if not
- Is there a way to backup the permissions separately and then reapply to
database once restored?
As a coda, I simply want to backup my db prior to making some changes and
want to know that it will restore OK if anything goes wrong (without
hassling the sysadmins). Am I going about it the best way?
Any comments much appreciated
IvorIvor
Do you perform RESTORE to another server or to the same ?
If you restore the database and the database does not exist you need to have
CREATE DATABASE permissons.
If the database does exist you need to be a member of db_owner fixed
database role ( if I remember well)
If you do RESTORE to a new server , search on internet for
'sp_help_revlogin' stored procedure that provided by MS to move LOGINS
between computers running SQL Server.
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>|||When you backup a database the permission in that database are backed up
with it. However, when you restore a backup on a different server, the
mapping between the users in the database and the logins on the server will
have been lost. The mapping goes via a generated ID for the login, and this
will be different on each server (and instance).
You can fix the mapping with the stored procedure sp_change_users_login, see
the KB article http://support.microsoft.com/kb/274188/ for the steps needed
to do this.
Jacco Schalkwijk
SQL Server MVP
"Ivor" <ivor.tillierREMOVETHIS@.westminster-pct.nhs.uk> wrote in message
news:%23f8PCGZzFHA.416@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> Sorry this is probably a bit of an old chestnut, but I couldn't see it in
> recent posts...
> I understand that when you backup a db using SQL database backup, you need
> to reapply the permissions when it is restored. (I notice that I have
> lost the permissions on the backup).
> 2 questions:
> - Is there a way to backup and keep the permissions?, if not
> - Is there a way to backup the permissions separately and then reapply to
> database once restored?
> As a coda, I simply want to backup my db prior to making some changes and
> want to know that it will restore OK if anything goes wrong (without
> hassling the sysadmins). Am I going about it the best way?
> Any comments much appreciated
> Ivor
>