Database Permissions

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jeff
    #1

    Database Permissions

    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
    Tags: None
    • Simon Hayes
      #2
      Re: Database Permissions

      jeffpuro@yahoo. com (Jeff) wrote in message news:<7851a310. 0401151322.4b8c f2e7@posting.go ogle.com>...[color=blue]
      > 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[/color]

      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

        Comment

        Previous template Next
        Working...