locked
User and Role and Collations question RRS feed

  • Question

  • Hi,

    In my SQL Server 2008 R2 standard, the collation of the master, model, msdb, tempdb is SQL_Latin1_General_CP1_CI_AS.  However, other databases's collation is different from the four system databases.
    If I change the 4 system databases' collation from SQL_Latin1_General_CP1_CI_AS to other, will it casuse any User/Role problem or any side effects?
    Thanks for help.

    Jason

    Friday, May 6, 2011 1:32 AM

Answers

  • You'll need to reinstall SQL Server or run setup with the REBUILDDATABSE action in order to change the system database collation.  This will recreate the system databases from scratch so you'll neeed to recreate server-level objects like logins, jobs, linked servers, etc. afterward.  You will also need to reattach the user databases afterwards. Database users authenticated with SQL logins will need to be remapped to the correct login usering ALTER USER,

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Peja Tao Monday, May 9, 2011 6:41 AM
    • Marked as answer by Alex Feng (SQL) Sunday, May 15, 2011 3:54 AM
    Friday, May 6, 2011 1:54 AM
  • As long as your columns are nchar or nvarchar, yes.  Collation does not affect the character set for Unicode data.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Peja Tao Monday, May 9, 2011 6:41 AM
    • Marked as answer by Alex Feng (SQL) Sunday, May 15, 2011 3:54 AM
    Friday, May 6, 2011 2:48 AM

All replies

  • You'll need to reinstall SQL Server or run setup with the REBUILDDATABSE action in order to change the system database collation.  This will recreate the system databases from scratch so you'll neeed to recreate server-level objects like logins, jobs, linked servers, etc. afterward.  You will also need to reattach the user databases afterwards. Database users authenticated with SQL logins will need to be remapped to the correct login usering ALTER USER,

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Peja Tao Monday, May 9, 2011 6:41 AM
    • Marked as answer by Alex Feng (SQL) Sunday, May 15, 2011 3:54 AM
    Friday, May 6, 2011 1:54 AM
  • You'll need to reinstall SQL Server or run setup with the REBUILDDATABSE action in order to change the system database collation.  This will recreate the system databases from scratch so you'll neeed to recreate server-level objects like logins, jobs, linked servers, etc. afterward.  You will also need to reattach the user databases afterwards. Database users authenticated with SQL logins will need to be remapped to the correct login usering ALTER USER, 
    Thanks!
    If I change other databases' collation to SQL_Latin1_General_CP1_CI_AS, will my SQL Server still be able to store the Chinese(Traditional) characters?
    Friday, May 6, 2011 2:02 AM
  • As long as your columns are nchar or nvarchar, yes.  Collation does not affect the character set for Unicode data.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Peja Tao Monday, May 9, 2011 6:41 AM
    • Marked as answer by Alex Feng (SQL) Sunday, May 15, 2011 3:54 AM
    Friday, May 6, 2011 2:48 AM