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 all rows from one table into another existing table and overwriting data

i have 2 tables (both containing the same column names/datatypes), say table1 and table2.. table1 is the most recent, but some rows were deleted on accident.. table2 was a backup that has all the data we need, but some of it is old, so what i want to do is overwrrite the rows in table 2 that also exist in table 1 with the table 1 rows, but the rows in table 2 that do not exist in table one, leave those as is.. both tables have a primary key, user_id.

any ideas on how i could do this easily?

thanksUSE Northwind
GO

SET NOCOUNT ON
SELECT * INTO myTable1 FROM Employees
GO

-- Have a Look

SELECT * FROM myTable1

-- Make a Backup

SELECT * INTO myTable2 FROM myTable1
GO

-- oops

DELETE FROM myTable1 WHERE EmployeeID BETWEEN 2 AND 6
GO

-- Have a Look

SELECT * FROM myTable1
GO

SET IDENTITY_INSERT myTable1 ON
GO

-- Recover the Rows

INSERT INTO myTable1 (
EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
)
SELECT EmployeeID
, LastName
, FirstName
, Title
, TitleOfCourtesy
, BirthDate
, HireDate
, Address
, City
, Region
, PostalCode
, Country
, HomePhone
, Extension
, Photo
, Notes
, ReportsTo
, PhotoPath
FROM myTable2
WHERE EmployeeID NOT IN (SELECT EmployeeID
FROM myTable1)
GO

SET IDENTITY_INSERT myTable1 OFF
GO

-- Have a Look

SELECT * FROM myTable1
GO

-- Clean up

DROP TABLE myTable1
DROP TABLE myTable2
GO

Copying a column names from one Database to another Database

Hello to everyone,
I am trying to check scripts when copying column names from one Database to
another Database.
If the Trigger is the answer. I don't know how to write the script properly
calling out the Database name.
Please help.
Thanks so much,Use the threepart name:
Select * from Database.Owner.Objectname
HTH, Jens Suessmeyer.|||Jens wrote:
>Use the threepart name:
>Select * from Database.Owner.Objectname
>HTH, Jens Suessmeyer.
Hi Jens,
Thanks for the attention. However I am not sure what does the "owner" means.
In my example, I need to copy the standard cost column from DB01 to standard
cost column of DB02. The table is Item master.
In this the correct syntax:
Update DB02.dbo(just guessing).stdost
Set DB02.dbo.stdcost=db1.dbo.stdcost
where dbo2.item no. = dbo1.item no.
Please let me if the above is right, else would like to request if you can
correct the above, many thanks!|||Close:
Update DB02.dbo.<tableName>.stdost
Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
Assuming that the owner is dbo (if not just leave it out -->
DB02..<tableName>.stdost) and the database is on the local server
otherwise you need a linked server entry.
HTH, jens Suessmeyer.|||Jens wrote:
>Close:
>Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo.DB02.item_no.
>Assuming that the owner is dbo (if not just leave it out -->
>DB02..<tableName>.stdost) and the database is on the local server
>otherwise you need a linked server entry.
>HTH, jens Suessmeyer.
Hi Jens
Thanks so much for your support,
May I further ask if I still using triggers? or the below is the whole synta
x
already?
Also I have question on DB01 of the 3rd line, please see below
Update DB02.dbo.<tableName>.stdost
>Set DB02.dbo.<tableName>.stdcost=db1.dbo.<tableName>.stdcost
>where DB02.dbo.<tableName>.item_no. = DB01.dbo."Should this be <tablename> instead
of DB02".item_no.
At any rate, kindly correct me. I hope I have the last questions on this
matter.
What a great world is this, thanks so much Jens God Bless
>
Message posted via http://www.webservertalk.com|||Sure, just a copy&paste error, sorry for that ;-)
But at the end you learned the syntax that fast, that you could even
find the errors :-)
HTH, Jens Suessmeyer.|||Jens wrote:
>Sure, just a copy&paste error, sorry for that ;-)
>But at the end you learned the syntax that fast, that you could even
>find the errors :-)
>HTH, Jens Suessmeyer.
I have you as my good tutor that's why, :-) Thanks so much ^.^
Message posted via http://www.webservertalk.com

Copyind data from one database table to other

hi all
i have two databases on two different machines.
both databses r having same names.
i want to copy data from the table in other database to table in databse on my machine .
how can i do this.
i will be very thankful to receive help.Take a look at DTS in sql book online. That's the easiest to start with.

copy/paste grid from QA - no field names!

for debug purposes i tried to copy paste some Query Analyser output into Excel... the data pastes fine, but i can't get the field-names to copy/paste.

is there a trick?

izyNot by itself...

YOu could save it as text...you get the column names that way...

Or you can Go

SELECT 'Col1', 'Col2', 'Ect'
UNION ALL
SELECT Col1, Col2, Ect
FROM your Table|||text! stupidly simple really.
thanks for the help,

izy|||Try results to Text & Tab delimited.
Under Tools --> Options --> Results.

It works OK for me.

Tim S

Copy tables between databases using variables

I am trying to find the best way to copy specific tables from one database
to another when the source and target database names are not always the
same. Can you use variables to specify (or prompt the user) to provide
source and target databases? The target database will exist with the the
same tables as the source. The tables to copy will always be the same.

Example:
UserA wants to copy 10 tables from Data1 to Data2
UserB wants to copy 10 tables from Data4 to Data5

I'm sure a script can do this in Query Analyzer but is there a more user
friendly method when the user has ony standard SQL tools?

Thanks in advance.Hi

You could use DTS and change the source/destination tables:
http://www.sqldts.com/default.aspx?213

You could use dynamic SQL if you don't open yourself to SQL injection:
http://www.sommarskog.se/dynamic_sql.html

John

<rdraider@.sbcglobal.net> wrote in message
news:5GRWb.23042$V57.1004@.newssvr27.news.prodigy.c om...
> I am trying to find the best way to copy specific tables from one database
> to another when the source and target database names are not always the
> same. Can you use variables to specify (or prompt the user) to provide
> source and target databases? The target database will exist with the the
> same tables as the source. The tables to copy will always be the same.
> Example:
> UserA wants to copy 10 tables from Data1 to Data2
> UserB wants to copy 10 tables from Data4 to Data5
> I'm sure a script can do this in Query Analyzer but is there a more user
> friendly method when the user has ony standard SQL tools?
> Thanks in advance.

Copy table column names from SSMS Object Browser to use in a query

I thought I saw this done once before. So today I hunted around in
Books OnLine and did a Google search. So far I have found nothing
close. So if you know how to do it, please tell me or if cannot be
done, I'd appreciate know that too.

Thanks in advance,
IanOOn Tue, 06 Nov 2007 18:17:05 -0000, iano wrote:

Quote:

Originally Posted by

>I thought I saw this done once before. So today I hunted around in
>Books OnLine and did a Google search. So far I have found nothing
>close. So if you know how to do it, please tell me or if cannot be
>done, I'd appreciate know that too.

Hi IanO,

Open Object Explorer with F8 (of you don't have it open yet), expand
"Databases", then the name of your DB, then "Tables", then the name of
your table. Now, drag the "Columns" map to the editor window and a list
of all columns will automagically appear.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||iano (IanONet@.gmail.com) writes:

Quote:

Originally Posted by

I thought I saw this done once before. So today I hunted around in
Books OnLine and did a Google search. So far I have found nothing
close. So if you know how to do it, please tell me or if cannot be
done, I'd appreciate know that too.

Find the table in Object Explorer and expand the note. Select Columns
and drag it to the place where you want it.

My own take on this is that I simply run a SELECT * with TOP 1 or
WHERE 1 = 0 in text mode. I copy and paste into window in Textpad
where I have a macro that replaces the multiple spaces with comma +
space. Sounds kludgy? Well, navigating in an explorer tree takes time too.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx