Changing Collations for master database.

Hi there
I am using SQL server 2000 and
I want to Change Server Collations from SQL_Latin1_General_CP850_BIN to
SQL_Latin1_General_CP1_CS_AS.
Can anybody help me in this regard.
Rgds
WilsonIf you have done absolutely nothing since installing SQL Server, you can just do this:

How to rebuild the master database (Rebuild Master utility)
To rebuild the master database

Shutdown Microsoft SQL Server 2000, and then run Rebuildm.exe. This is located in the Program Files\Microsoft SQL Server\80\Tools\Binn directory.

In the Rebuild Master dialog box, click Browse.

In the Browse for Folder dialog box, select the \Data folder on the SQL Server 2000 compact disc or in the shared network directory from which SQL Server 2000 was installed, and then click OK.

Click Settings. In the Collation Settings dialog box, verify or change settings used for the master database and all other databases.
Initially, the default collation settings are shown, but these may not match the collation selected during setup. You can select the same settings used during setup or select new collation settings. When done, click OK.

In the Rebuild Master dialog box, click Rebuild to start the process.
The Rebuild Master utility reinstalls the master database.

Note To continue, you may need to stop a server that is running.


Otherwise, you need to look at Books Online. It's much more complicated. Here's what BOL has to say about after:

After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft SQL Server 2000 you must:

Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.

Export all of your data using a tool such as bulk copy.

Drop all of the user databases.

Rebuild the master database specifying the new collation.

Create all of the databases and all of the objects in them.

Import all of your data.

Note Instead of changing the default collation of an instance of SQL Server 2000, you can specify a default collation for each new database you create.|||Thanks for ur response.

But can you tell me which option should I select to install SQL_Latin1_General_CP1_CS_AS.

as I can see only one option for CS and AS and when I choose that option(Dictionary order,case-sensitive,ascent sensitive for use with 1253(Greek) character set, my collation comes out to be Latin1_General_CP1_CS_AS.
though I am looking for SQL_Latin1_General_CP1_CS_AS.|||They are the same thing.|||Note on the below:

CREATE TABLE test(test_collate VARCHAR(255) COLLATE Latin1_General_CP1_CS_AS)
CREATE TABLE test(test_collate VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS)

The first will give you an invalid collation name. The second will compile. When you select the options you mentioned above, you are creating the second example. Make sense?|||ALTER DATABASE name_database COLLATE SQL_Latin1_General_CP1_CS_AS

Changes to Master/Target Server Configurations

I've installed SQL Server 2000 and applied SP3a. I see SP4 is available and fixes a lot of things. The only bit I am unsure about is section '5.3.2 Changes to Master/Target Server Configurations'

http://download.microsoft.com/download/1/b/d/1bdf5b78-584e-4de0-b36f-c44e06b0d2a3/ReadmeSql2k32sp4.htm#_5464_sql_server_agent_enhancements_705

"Before you apply SP4, you must complete several steps to upgrade your SQL Server 2000 master/target server configuration. The changes that are introduced with SP4 are not compatible with SQL Server 7.0 target servers, or with any servers not running SP3 or later. This is a change from the original SQL Server 2000 functionality. "

As I'm just starting out I don't even know what 'Master/Target Server' is. I'm only installing to my PC which is XP Pro SP2. Can I ignore this step and apply the patch or do I need to do as it states?

Thanks

SQL Server 2000 service pack 3 and 3a are twenty five .sql files Windows uses as needed, I have used them for all existing operating systems with minor problems with Windows 2000 standard server. I would say just run it and in SQL Server 2000 and below ignore most questions only answer those relevant to your situation and you will not run into problems with SQL Server. Hope this helps.

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. For 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 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...
> 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. 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 things 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...
> > 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. George
>|||> 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 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...
> 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 things 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...
>> > 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. George
>>|||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 the
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:
> > 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 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...
> > 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 things 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...
> >> > 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. George
> >>
> >>
>

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 Password on Database Master Key

We are implementing PCI (Payment Card Industry) and one thing that is required is that we change our encryption keys once a year or when a key custodian leaves. The only way that I have seen to do that is to decrypt all the encrypted data, drop and recreate the symmetric key, the certificate, and database master key, and then re-encrypt the data.

Is there any way to just change the password on the database master key? I understand that if the actual symmetric keys have been breached, that would not be enough, but for routine maintenance, just changing the password on the database master key would seem to be enough.

I have looked and looked, but have not found anyone else that is doing this type of thing. Maybe I am just not looking in the right places. Can anyone offer some guidance? Thank!!!

Jim Youmans

You can regenerate the database master keys (DbMK) using the REGENERATE clause of the ALTER statement. This only re-encrypts the keys encrypted by the DbMK, not the entire set of encrypted data. It is also possible to change the password of a master key; you can have in fact multiple password encryptions of a master key, so you can add a new password encryption and drop the old one.

You can also change the certificate encrypting a key - just encrypt the key with a new certificate and then drop the encryption done with the old certificate.

If you actually want to change the key that encrypts the data, then there is no other way than by decrypting the data and re-encrypting it with a new key.

Thanks
Laurentiu

|||

Thank you so much!!!

Jim Youmans

Change ownership of model and msdb to 'sa'

From BOL:
The owner of the master, model, or tempdb system databases
cannot be changed.
- Rob

>--Original Message--
>We are trying to exec sp_changedbowner 'sa' on model
database to change
>ownership from the service account 'service_sql' to 'sa'
account
>However, we got the follow error:
>15109 16 Cannot change the owner of the master database.
>Is there any know how to Change ownership of model and
msdb to 'sa' if in
>case 'sa' is not owner of msdb and model?
>Thanks!!
>.
>
I am facing the same problem as Maggie. My Production's SQL2k MSDB & Model
database owner is my old NT Domain Administrator (olddomain/administrator).
Not sure how it ended that way. Master owner is SA.
We have completed domain migration to Active Directory, if sp_changedbowner
is not going to allow me to change DB owner for MSDB & Model, does that mean
I still got to keep my old domain forever ?
There must be a work-around for this.
"Rob" wrote:

> From BOL:
> The owner of the master, model, or tempdb system databases
> cannot be changed.
> - Rob
> database to change
> account
> msdb to 'sa' if in
>

Change ownership of model and msdb to 'sa'

From BOL:
The owner of the master, model, or tempdb system databases
cannot be changed.
- Rob

>--Original Message--
>We are trying to exec sp_changedbowner 'sa' on model
database to change
>ownership from the service account 'service_sql' to 'sa'
account
>However, we got the follow error:
>15109 16 Cannot change the owner of the master database.
>Is there any know how to Change ownership of model and
msdb to 'sa' if in
>case 'sa' is not owner of msdb and model?
>Thanks!!
>.
>I am facing the same problem as Maggie. My Production's SQL2k MSDB & Model
database owner is my old NT Domain Administrator (olddomain/administrator).
Not sure how it ended that way. Master owner is SA.
We have completed domain migration to Active Directory, if sp_changedbowner
is not going to allow me to change DB owner for MSDB & Model, does that mean
I still got to keep my old domain forever ?
There must be a work-around for this.
"Rob" wrote:

> From BOL:
> The owner of the master, model, or tempdb system databases
> cannot be changed.
> - Rob
>
> database to change
> account
> msdb to 'sa' if in
>