colabus


Hi guys,

I've been assigned the task of setting up access to our SQL Server 2005 box. A consultant developing for us has accessing to 2 databases and I've set this up fine. It appears however that one of these databases is re-copied over to the server every night to keep data reasonably current.

I'm not interesting in changing this method as I'm not the maintainer (as yet).

Basically I would like to know if I've setup access to this database (it works fine), when the database is updated (with an SSIS package) the account seems to get deleted. Do the original permissions from the source database overwrite those of its destination

Cheers




Re: Does SSIS database copy overwrite existing user permission to that database?

DarrenSQLIS


Permissions are part of the DB. Without knowing exactly what tasks have been used, and how they have been configured then I am guessing really, it could go either way. Is this a pseudo merge or object copy, or an entire DB copy

You say this is copy, so perhaps they have used the Transfer Database Task. The Transfer Database Task has an OverwriteDestination property, so any user or permissions changes created during the day will be lost each night as it is a new DB. Seems reasonable. If you need a setting or object that does not exist in the source DB, you will always need to re-create it after the copy.

(Remember the distinction between a login and a user, server vs database.)