how to prevent security/permissions affected by backup/restore RRS feed

  • Question

  • I create a copy of a production database (for reporting purposes) by using the backup and restore commands.  The copy is on a seperate server. (replication wont work in my case)
    The reporting server allows more read-only access then the production server.

    I do a full backup and restore, and of course all permissions/security settings come with all the data.

    How can I do a backup and restore of only data without altering permissions on the reporting server? 
    As more of my users want to run reports, I give them access on the reporting server but not the production server, and of course it gets wiped out every night when the backup-restore runs.

    Using SQL Server 8.0 and 9.0.
    Thursday, March 4, 2010 4:01 PM

All replies

  • You would have to run a script post restore everyday or if the users exists already and there are mismatch in SID between users and login then you have to use sp_change_users_login.

    Write something like
    Use dbname
    sp_addrolemember 'db_datareader','loginname'-- and schedule this on a regular basis when the restore copletes.
    Thanks, Leks
    Thursday, March 4, 2010 4:58 PM
  • I was hoping to find another way, instead of a seperate script with all the permissions in it.
    Best case would be some options/switches in the restore command that does not overwrite the permissions.  I didnt see anything like that in the documentation, I guess it isnt available.
    Tuesday, March 9, 2010 6:12 PM
  • NO. This has to be done manually. With SQL server security architecture login is the first level and user / role are secondary and an user has to be always associated with a login. By restoring a db you only move the user inside that db and not a login (as it is out of the scope of db). It’s the responsibility of a DBA to look at fixing the mismatch login and users once the restore is completed.


    Thanks, Leks
    Tuesday, March 9, 2010 8:19 PM