Change the collation on the user DB

Changed the master db collation by using the rebuildm utility from
SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change the
user dbs now? Issue is critical. Please help. GeorgeALTER DATABASE. But that doesn't change collation for the existing tables. F
or that you use ALTER
TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other things
you need to do (like
dropping indexes), see Books Online. You can search the archives to see if a
nyone has posted a
script to semi-automate this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
> Changed the master db collation by using the rebuildm utility from
> SQL_LATIN1_GENERAL_SP1_CI_AS to Latin1-General, Binary. How do I change th
e
> user dbs now? Issue is critical. Please help. George|||Tibor,
is it possible to dump the database to a flat file, delete the existing db,
create a new db with the correct collation and then import data back in? I
tried backup, delete resotore but it changed back to what the database was
when it was backed up. Any help on this is greatly appreciated. George
"Tibor Karaszi" wrote:

> ALTER DATABASE. But that doesn't change collation for the existing tables.
For that you use ALTER
> TABLE ... ALTER COLUMN. Yes, a lot of work, and there are some other thing
s you need to do (like
> dropping indexes), see Books Online. You can search the archives to see if
anyone has posted a
> script to semi-automate this.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:849B0CB4-1487-441A-B91B-41DA1C87C37A@.microsoft.com...
>|||> is it possible to dump the database to a flat file
There is no such direct feature in SQL Server. But you can use some of the t
ransfer tools (DTS etc),
and depending on which method you chose, you script your objects first and t
hen re-create them
before the import.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"George" <George@.discussions.microsoft.com> wrote in message
news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...[vbcol=seagreen]
> Tibor,
> is it possible to dump the database to a flat file, delete the existing db
,
> create a new db with the correct collation and then import data back in? I
> tried backup, delete resotore but it changed back to what the database was
> when it was backed up. Any help on this is greatly appreciated. George
> "Tibor Karaszi" wrote:
>|||Had to call Microsoft. If you change only one db (user db) then follow these
instructions:
Transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000
To transfer a database from one collation in SQL Server 2000 to a different
collation in SQL Server 2000, follow these steps:
1. Back up the source database.
2. Note if any columns use the COLLATE clause.
3. Create a new database on the destination server with the appropriate
collation.
4. If no columns use the COLLATE clause, use DTS to transfer the data (Right
click on the database, Export data) to the destination server. To do so,
enable the Use Collation option (choose third option when choosind type of
transfer which is all data) for code page translation and to transfer the
data to the new collation on the destination database. If any columns use th
e
COLLATE clause, follow these steps: a. Generate scripts for all the objects
(not including the indexes, the triggers, the primary keys, the foreign keys
,
the default settings, and the constraints). Additionally, make sure that you
enable the Only script 7.0 compatible features option to remove the COLLATE
clause from the script.
Note When you use the Only script 7.0 compatible features option, you can
change the collation. However, any new SQL Server 2000 options (including
user-defined functions, extended properties, the INSTEAD OF trigger, and
indexes on views) will not be considered when the scripts are generated.
b. Run the scripts from step a on the destination database to create the
objects with the destination database collation.
c. Use DTS to transfer only the data from the source database.
d. After the data is successfully transferred, generate scripts for all the
constraints, foreign keys, primary keys, and indexes from the source
database.
e. Run the scripts from step d on the destination database.
George
"Tibor Karaszi" wrote:

> There is no such direct feature in SQL Server. But you can use some of the
transfer tools (DTS etc),
> and depending on which method you chose, you script your objects first and
then re-create them
> before the import.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "George" <George@.discussions.microsoft.com> wrote in message
> news:F6F90F12-1FC5-450B-8724-DD3D5DCA5C71@.microsoft.com...
>

Change Ownership

How do I change ownership of a database table to another user?
I did a restore of a database using our database software. It retained the
user's ownership of a table, but did not restore the users, so basically i
have a user that does not exist to this server owning a table.. confusing!!
I would like to give ownership of this table to a new user I create for this
database. SQL server will not let me create a user with the same name b/c it
says the user exist...it does not. I'm very confused and need help thanks. I
hope this helps.. We are running SQL 2000 SP3a on Windows 2000 Server SP4.Look up sp_changedbowner in BOL (Books OnLine).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Adrian Moseley" <amoseley@.personix.fiserv.com> wrote in message
news:%23RxUpJmHEHA.3832@.TK2MSFTNGP10.phx.gbl...
> How do I change ownership of a database table to another user?
> I did a restore of a database using our database software. It retained the
> user's ownership of a table, but did not restore the users, so basically i
> have a user that does not exist to this server owning a table..
confusing!!
> I would like to give ownership of this table to a new user I create for
this
> database. SQL server will not let me create a user with the same name b/c
it
> says the user exist...it does not. I'm very confused and need help thanks.
I
> hope this helps.. We are running SQL 2000 SP3a on Windows 2000 Server SP4.
>|||Hi,
To Add on,
Since you did a restore of database actually your database users also got
restored in the new database, but the lost the SID chain with Master
database syslogins tables. Due to this you will not be able to see the users
either thru enterprise manager or sp_helpuser procedure. But if you query
the "sysusers" table in corresponding database you will be able to see them.
The brink back the chanin you have to execute the procedure
Use <dbname>
go
sp_change_users_login 'Report'
This will Lists the users, and their corresponding security identifiers
(SID), that are in the current database, not linked to any login.
Based on the output you can use 'Update_one' option to rectify the isue.
Refer books online for more information.
Thanks
Hari
MCDBA
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:O5zu#rmHEHA.2924@.TK2MSFTNGP09.phx.gbl...
> Look up sp_changedbowner in BOL (Books OnLine).
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Adrian Moseley" <amoseley@.personix.fiserv.com> wrote in message
> news:%23RxUpJmHEHA.3832@.TK2MSFTNGP10.phx.gbl...
the
i
> confusing!!
> this
b/c
> it
thanks.
> I
SP4.
>