Re: DTS package to copy database

By far the best way is to use BACKUP and RESTORE. In my expereince and a
lot of others this is the cleanest way.

--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Uday Deo" <UdayDeo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:953D1B44-506E-49FB-9799-9FA947BB131F@xxxxxxxxxxxxxxxx
> Hi everyone,
>
> I am copying a database from one machine to another.
> The DB permissions are set for local users on each machine (including
> IUSER_WEBSERVER1 and IUSER_WEBSERVER2 etc).
>
> While copying the database I am using copy SQL Server Objects Task.
> Under Copy Tab,
> I am selecting create dest. objects (including drop destination objects
> first, include all dependent objects, Include extended properties), copy
> data
> (replace existing data) Use collation and Copy all objects.
>
> Under default options I am not copying DB users and roles, sql server
> logins, object level permissions since the users are diffenent on both
> machines.
>
> The basic aim is just transfer the data from one DB to another. I am
> having
> hard time to manage the permissions.
>
> Any thoughts about doing it efficiently without mapping permissions for
> the
> objects i.e. keeping the permissions as is and just clean the data and
> copy
> new / recent data from prod to staging.
>
> Uday


.

Relevant Pages

  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie: I dont understand user permissions for table access
    ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
    (microsoft.public.sqlserver.server)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server is creating a job behind the scenes. ... SA account password and gaining access to the database. ... > SQL Server doesn't check permissions on indirectly referenced objects as ... > the proxy account security context for non-sysadmin users from Enterprise ...
    (microsoft.public.sqlserver.security)
  • Re: Security question ..
    ... What I want to prevent is any access to the database accept through our ... application unless you have elevated permissions. ... Authentication, if he is smart enough to create an NT Auth ODBC connection ... passes through to the database or to use SQL Server authentication. ...
    (microsoft.public.sqlserver.server)
Loading