RE: Domain name change for user group security

• Previous message: Poch Reyes: "SQL Server 2000 Audit"
• In reply to: Jennifer Fry: "Domain name change for user group security"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Wed, 28 May 2003 06:17:07 GMT

Hi Jennifer,

>From the following article, I find some information.
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872

Map the Standard and Integrated Logins
--------------------------------------

 After you move a database from one server that is running SQL
                  Server server to another server that is running SQL Server server,
follow these
                  steps for minimal user intervention:
                  

1. Make sure that there is a login in the sysxlogins table in the
   master database for each user in the sysusers table of the database.
   NOTE: To add a standard SQL Server login, see the "sp_addlogin" topic
   in SQL Server Books Online. To add an integrated SQL Server login, see
   the "sp_grantlogin" topic in SQL Server Books Online.

2. Download the MapSids.exe file, and then extract the Sp_sidmap.sql
   and Readme.txt files.

3. Log on to the server that is running SQL Server as a system
   administrator, and then run the Sp_sidmap.sql file in the user
   database. Running the Sp_sidmap.sql file creates the two stored
   procedures, sp_sidmap and sp_prefix_sysusersname.

4. Make sure that the database is not accessed by any other user than
   the one who is running the stored procedures.

5. Make sure that Query Analyzer displays results in text format and
   not in grid format. To do so, either press the "CTRL^T" (without the
   quotation marks) keys or click Query, and then click "Results in Text".
   This is very important so that you can view the results and the
   informational messages in one window and save the output to a text
   file. You might need this file later to resolve some of the mappings.

6. Because there is no way to verify whether the parameters
                                are passed correctly, make sure to pass them correctly to the sp_sidmap
stored procedure:

                
                EXEC sp_SidMap @old_domain = old_domain_name,
                @new_domain = new_domain_name,
                @old_server = old_server_name,
                @new_server = new_server_name Replace the values for the old and new
domain names and server names
                                appropriately.

7. Save the results in a file and follow the directions that are
   provided in the Readme.txt file. NOTE: When you run these stored
   procedures, the sysusers table is the only table that changes in the
   database. To return to a state where you started, restore the database
   from the backup or reattach the database.

For more information, check the following article.
SAMPLE: Mapsids.exe Helps Map SIDs Between User and Master Databases When
Database Is Moved
http://support.microsoft.com/default.aspx?scid=298897

This posting is provided "AS IS" with no warranties, and confers no rights.

Regards,
  
Bill Cheng
Microsoft Support Engineer
--------------------
| Content-Class: urn:content-classes:message
| From: "Jennifer Fry" <jfry@grede.com>
| Sender: "Jennifer Fry" <jfry@grede.com>
| Subject: Domain name change for user group security
| Date: Tue, 27 May 2003 08:34:17 -0700
| Lines: 12
| Message-ID: <334601c32465$6f241b80$a501280a@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Thread-Index: AcMkZW8kRor5Nt+PSYeD3y+tzxPe/A==
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Newsgroups: microsoft.public.sqlserver.security
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.security:13698
| NNTP-Posting-Host: TK2MSFTNGXA13 10.40.1.165
| X-Tomcat-NG: microsoft.public.sqlserver.security
|
| This past weekend we added a new domain. All of our user
| groups added to SQL server were authenticated through the
| old domain (CompanyNT/UserGroup). How do I update the
| groups in SQL server to authenticate through the new
| domain (CompanyW2k/UserGroup)? I really don't want to
| have to manually re-create them and re-assign permissions.
|
| I'm assuming that I am going to have to do some scripting
| for this, but I'm not sure if there is a stored procedure
| that I can run that will simplify it or not.
|
| Thanks in advance for your help.
|

• Previous message: Poch Reyes: "SQL Server 2000 Audit"
• In reply to: Jennifer Fry: "Domain name change for user group security"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]