Correct invalid SID

I migrated my SQL 2000 secuity from one NT Domain to another. In the process of changing the login names in the master..sysxlogins table, the SID did not get updated.

Is there an easy way to correct the SID entry without dropping and recreating each user?

Do you mean that you have manually changed the login names in sysxlogins and you wish to update the SID entries as well? Can you explain what you meant by "the process of changing the login names in the master..sysxlogins table"?

Thanks
Laurentiu

|||

I ran the following in the master db:

UPDATE sysxlogins

Set [name] = 'NEWDOMAIN\' + substring([name], patindex('%\%', [name])+1, 200)

WHERE [name] like 'OLDDOMAIN\%'

It changed all the login names to point to the new domain. The problem was the SIDs changed (which I didn't think about) in the new domain. Users can get in, but when we try to use the function suser_sid(), the correct network sid is being returned and we cant compare it to the one in sysxlogins or sysusers because it doesn't match.

|||

If you move from domain A to domain B, the Windows logins from domain A are normally invalidated. An exception to this would be if domain B was trusted by domain A, then you could still use the A logins even though the server runs in domain B.

This kind of domain change is not a supported operation. There is no supported solution for fixing this. It's not only the logins that you would need to fix, but all the database users as well. If you plan to change the domain for your server often, then you should use only SQL authentication.

Thanks
Laurentiu

sql

Copying columns to table with different design

I am working with a SQL database that was migrated from MS Access and adapted for full-text search which involved creating a new table with a different design.I would like to copy three columns (YearGiven, MonthGiven, DayGiven) from the Documents table to the newly-created FullDocuments table so that I can delete the Documents table and four other tables with redundant data.Here are the two tables involved in the column copy:

Documents Table

DocID (Primary Key)

DocNo

SequenceNo

YearGiven

MonthGiven

DayGiven

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (empty – no data)

MonthGiven (empty – no data)

DayGiven (empty – no data)

After the copy column procedure I want the FullDocuments table structure to look like this:

FullDocuments Table

FullDocID (Primary Key)

DocNo

SequenceNo

SectionText

YearGiven (full of transferred data)

MonthGiven (full of transferred data)

DayGiven (full of transferred data)

The problem is that the FullDocuments table contains approximately 4x as many rows as the Documents table.This is because each document has four types – the primary Text document and three supplemental documents (Background, Report, and Index).Whereas the Documents table has one row for each Document, the Full Documents table has four rows per document (the four document types).Instead of simply doing a copy and paste of columns based on a primary key and foreign key relationship (as described in the MSDN online books), I need to copy and paste based on the DocNo and SequenceNo columns.Thus the same date data (Year, Month, Day) will need to occupy four consecutive rows for each document in the FullDocuments table (as is now the case with the DocNo and SequenceNo).

I will end up with one table that meets the requirements for the full-text search (primary key and all the text to be searched) and eliminate five tables (Documents and the four document type text tables).This is a static database consisting of historical records so I am not concerned about input errors that can be associated with denormalization.

I tried exporting the data using the Export Wizard using the “Copy data from one or more tables” option.The transfer failed.The most relevant lines of the Error Report are:

·Error 0xc0202009: Data Flow Task: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "The statement has been terminated.".
An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Cannot insert the value NULL into column 'FullDocumentID', table 'Documents.dbo.FullDocuments'; column does not allow nulls. INSERT fails.".
(SQL Server Import and Export Wizard)

·Error 0xc0047022: Data Flow Task: The ProcessInput method on component "Destination - FullDocuments" (61) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
(SQL Server Import and Export Wizard)

·Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0202009.
(SQL Server Import and Export Wizard)

I then tried the “Write a query to specify the data to transfer” option focusing on just the YearGiven column using various FETCH, INSERT AND UPDATE commands without any luck.Questions:

1.Is the query approach the best way to achieve my objective, or did I miss something in the “Copy data” option in the Export Wizard?

2.If the query approach is the best way, any suggestions on what the query will look like?

Thanks for any help you can provide.

It sounds as though you have a pretty complicated requirement here and therefore I would suggest you try to build a package using BIDS rather than using the import/export wizard.

-Jamie

|||Jamie: Thanks for the suggestion. It would never have occurred to me to use the Business Intelligence Development Studio for this purpose since I think of BIDS as a report generator instead of a tool for moving data around in a database. I'll have to look into this option.|||

BIDS is the tool for building Reporting Services reports, Analysis Services cubes, and Integration Services packages. Hence its a tool for building BI components - hence its name.

-Jamie

Copying a project. How?

We recently migrated to SQL2005 and have moved all OLAP databases to the new server. I need to create new OLAP databases. In SQL2000, just a copy would do it, but I am having problem in 2005. I tried to script the database as 'create' or even created an empty one and try to change it by 'alter' from a valid DB, but no luck (XMLA file was modified to have the new names).
Any pointers, suggestion would be appreciated. I know how to change the DataSource and DataView later, just need to know how to copy one!!!!
Also please let me know what tool is used (Management Studio, Visual Studio, etc...)
Thanks in advance!

There is a special project in BIDS where you can reverse engineer a SSAS 2005 database.

If you have a BIDS project with a cube you can choose to save the whole project under a new name.

HTH

Thomas Ivarsson

|||I used BIDS, made a new 'Import Analysis Services 9.0 Database' from the template and imported a working database with everything. <-- Great.
Then made a new DataSource pointing to the right source and changed the DSV in XML to point to it <-- Great
Problem now is that, it is throwing and error that The 'Role' with 'ID' ='suchandsuch' doesn't exist in the collection when I go to build it. I saw this Role in the DataSource when viewed in XML and obviously can change it, but will not compile even when changed to a more previleged role. Same error!
The other OLAP database runs fine.
Any comments, suggestion will be appreciated as always, as I hope this info will help the next person.
noorbakhsh|||Got it. The above will fix the problem except we had a removed user which I had to remove from users in roles manually. Also the database I copied was corrupted so when I re-did it it worked fine.
I hope this helps others!
noorbakhsh|||One more follow-up since the other stuff had errors and there was an easier way:

I used BIDS, made a new

'Import Analysis Services 9.0 Database' from the template with the new databse name and imported

a working database with everything. <-- Great.

Then I just right-clicked on the DataSource/View Designer and 'edit' the connection string pointing to the new DataBase.

Right clicked on the database/properties and changed the deployment parameters

Deploy - Done

I hope this helps!!! - It should :)

noorbakhsh