Database Permissions

[Jeff]

We have a system at work that copies (using DTS) over databases from
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?

Thanks,

Jeff

[Simon Hayes]

je******@yahoo.com (Jeff) wrote in message news:<78**************************@posting.google. com>...

We have a system at work that copies (using DTS) over databases from
one SQL Server box to another every night. The copying process drops
each target object and then recreates them. On the 2nd SQL Server box
I have an account setup that is supposed to only have access to run
queries on the databases that get copied over every night. However,
because the DTS packages are dropping the objects first we are losing
the table level permissions for this user, so this user can't access
these databases the next day. Is there a way to automate resetting
these permissions on each table in the databases? Perhaps I should be
using replication as opposed to DTS packages for copying over entire
databases? If I used replication, would this avoid losing the
permissions that I need?

Thanks,

Jeff

You could script the table permissions, then execute that script after
copying the objects. Although it's not clear from your post why you
always drop and recreate the objects - DTS can copy only the data, or
you can put it in staging tables first, then insert into the final
production tables if you have some mapping logic. Replication would be
an option (perhaps snapshot replication), but it can be complex.

Simon