Database Access

I run several DTS Packages that load databases residing on two seperate
instances of SQL Server 2000 on the same server. For security reasons, I
need to be able to set the Databases on one instance to Read/Write access,
set the database access on the other instance to Read only, run the packages
on the first instance, and then reset the access to Read Only. The ideal
solution would be writing the logic in T-SQL in a stored procedure that is
already part of the Package or add a new stored procedure to the package. I
have not been able to find any reference to setting the Database access in
code. I would appreciate and welcome any advise on how to accomplish this
programming task. Thank you.
grp
Have a look at sp_dboption in bol.
You might be better off creating roles with the correct permissions and
moving the user between those roles.
"PGene2" wrote:

> I run several DTS Packages that load databases residing on two seperate
> instances of SQL Server 2000 on the same server. For security reasons, I
> need to be able to set the Databases on one instance to Read/Write access,
> set the database access on the other instance to Read only, run the packages
> on the first instance, and then reset the access to Read Only. The ideal
> solution would be writing the logic in T-SQL in a stored procedure that is
> already part of the Package or add a new stored procedure to the package. I
> have not been able to find any reference to setting the Database access in
> code. I would appreciate and welcome any advise on how to accomplish this
> programming task. Thank you.
> --
> grp
|||Thanks. This at least gives me a couple of directions to go in. I appreciate
the help.
grp
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Have a look at sp_dboption in bol.
> You might be better off creating roles with the correct permissions and
> moving the user between those roles.
>
> "PGene2" wrote:

Database Access

I run several DTS Packages that load databases residing on two seperate
instances of SQL Server 2000 on the same server. For security reasons, I
need to be able to set the Databases on one instance to Read/Write access,
set the database access on the other instance to Read only, run the packages
on the first instance, and then reset the access to Read Only. The ideal
solution would be writing the logic in T-SQL in a stored procedure that is
already part of the Package or add a new stored procedure to the package. I
have not been able to find any reference to setting the Database access in
code. I would appreciate and welcome any advise on how to accomplish this
programming task. Thank you.
grpHave a look at sp_dboption in bol.
You might be better off creating roles with the correct permissions and
moving the user between those roles.
"PGene2" wrote:

> I run several DTS Packages that load databases residing on two seperate
> instances of SQL Server 2000 on the same server. For security reasons, I
> need to be able to set the Databases on one instance to Read/Write access,
> set the database access on the other instance to Read only, run the packag
es
> on the first instance, and then reset the access to Read Only. The ideal
> solution would be writing the logic in T-SQL in a stored procedure that is
> already part of the Package or add a new stored procedure to the package.
I
> have not been able to find any reference to setting the Database access in
> code. I would appreciate and welcome any advise on how to accomplish this
> programming task. Thank you.
> --
> grp|||Thanks. This at least gives me a couple of directions to go in. I appreciat
e
the help.
--
grp
"Nigel Rivett" wrote:
[vbcol=seagreen]
> Have a look at sp_dboption in bol.
> You might be better off creating roles with the correct permissions and
> moving the user between those roles.
>
> "PGene2" wrote:
>

Data Transformation Services Migration wizard

i am trying to use the DTS migration wizard in sql server 2005 to migrate some of the DTS packages that i have on sql server 2000.

After entering the source and destination server i get the following error:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index (mscorlib)

Does anyone know the reson behind this?

Thanks for any help.

I've just installed Server 2005 and am getting the same message. The earlier threads refer to special characters and leading or trailing spaces. I've tried the wizard on a few packages that have nothing but letters in the name, and I get the above message. I tried repairing .NET 2.0 as well (didn't work).

What else should I/we try?

Thanks,

K

|||

Found a forum where a user clarified that NONE of the DTS packages can have a leading/trailing space. Well, one out of a hundred or so packages had a space; after I fixed that one, the wizard worked.

Find the spaces (thanks to Joseph Sack's SQL Server blog):

SELECT DISTINCT name
FROM msdb.dbo.sysdtspackages
WHERE name LIKE '% '

and I'd suggest: or name LIKE ' %'

|||

Thanks a lot.

I had one package that had a space. After i deleted the space i was able to get a little further. but when i hit finish, All the packages display "Stopped" and none gets transferred.

Thanks