backuping logins + passwords.

I can backup my logins but without passwords....using SQL 2005 , generate sql scripts...

any idea i can generate a SQL script with the passwords in cleartext? i like to recreate them in my test database later.

As MSSQL 2005 doesn't store passwords (it stores only password hashes), there is no way to get them in clear text. If you want to back up your logins, you should backup master database; if you want to thransfer them to another server, you should use the DTS "Transfer Login Task" (just don't know analogue in MSSQL 2005 SSIS, it is possible that task didn't change the name).

Anyway, maybe it would be useful : http://search.microsoft.com/results.aspx?q=Transfer+Logins+and+Passwords+Between+Instances+of+SQL+Server&l=1&mkt=en-US&FORM=QBME1

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

go

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

go

create login companyA default_database 'customersA'

go

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

go

use CustomersA

go

sp_changedbowner 'CustomersA'

go

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

go

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

go

create login companyA default_database 'customersA'

go

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

go

use CustomersA

go

sp_changedbowner 'CustomersA'

go

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar