locked
How to recreate SQL server logins RRS feed

  • Question

  • I deleted these groups from the SQL server logins by mistake.

    ServerName\SQLServer2005MSFTEUser$ServerName$InstanceName
    ServerName\SQLServer2005MSSQLUser$ServerName$InstanceName
    ServerName\SQLServer2005SQLAgentUser$ServerName$InstanceName.

    If anyone could point me towards directions on how to recreate these logins I would greatly appreciate it.

    Monday, June 21, 2010 8:08 PM

Answers

  • Those are local windows groups that are used to manage SQL Server.  You can add the logins back by adding a windows group as the login, just like you would any other windows group.  Just select the groups from the local server and not the AD domain.
    "willo2010" wrote in message news:b649047b-7c4d-429b-b7f4-8fb47f476874...

    I deleted these groups from the SQL server logins by mistake.

    ServerName\SQLServer2005MSFTEUser$ServerName$InstanceName
    ServerName\SQLServer2005MSSQLUser$ServerName$InstanceName
    ServerName\SQLServer2005SQLAgentUser$ServerName$InstanceName.

    If anyone could point me towards directions on how to recreate these logins I would greatly appreciate it.

    • Proposed as answer by Tom Li - MSFT Thursday, June 24, 2010 10:53 AM
    • Marked as answer by Tom Li - MSFT Sunday, June 27, 2010 9:55 AM
    Tuesday, June 22, 2010 1:52 AM
  • Hi,

    If you have a backup copy of master database which has the deleted logins information (i.e. before deleting the server logins) you can follow the following steps.

    If your server is Production environment

    1. Take any test environment and copy the master databse .mdf and .ldf files to some other location.

    2. Up the test SQL server services and restore the master database backup file.

    3. Generate the login script on test server. (How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008 : http://support.microsoft.com/kb/918992/en-us)

    4. took the create login statements for the dropped logins.

    5. run that login script on prod server.

    6. execute the sp_change_users_login SP on all databases to fix the orphaned logins, if any.

    If your server is Dev environment you can try the above steps on the same server.

    --------------------------

    Thanks,

    RajaSekhar Reddy .K

     

    • Proposed as answer by Tom Li - MSFT Thursday, June 24, 2010 10:52 AM
    • Marked as answer by Tom Li - MSFT Sunday, June 27, 2010 9:55 AM
    Tuesday, June 22, 2010 6:02 AM
  • I deleted these groups from the SQL server logins by mistake.

    ServerName\SQLServer2005MSFTEUser$ServerName$InstanceName
    ServerName\SQLServer2005MSSQLUser$ServerName$InstanceName
    ServerName\SQLServer2005SQLAgentUser$ServerName$InstanceName.

    If anyone could point me towards directions on how to recreate these logins I would greatly appreciate it.


    It is that simple , execute the below scripts and assign necessary privileges .

     

    /****** Object:  Login [MACHINENAME\SQLServer2005SQLAgentUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:12 ******/

    /****** Object:  Login [MACHINENAME\SQLServer2005SQLAgentUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:12 ******/

    CREATE LOGIN [MACHINENAME\SQLServer2005SQLAgentUser$MACHINENAME$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

    /****** Object:  Login [MACHINENAME\SQLServer2005MSSQLUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:09 ******/

    /****** Object:  Login [MACHINENAME\SQLServer2005MSSQLUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:09 ******/

    CREATE LOGIN [MACHINENAME\SQLServer2005MSSQLUser$MACHINENAME$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

    /****** Object:  Login [MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:44:44 ******/

    /****** Object:  Login [MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:44:44 ******/

    CREATE LOGIN [MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]


    Thanks, Leks

    Replace the MACHINENAME with your local system name

    • Proposed as answer by Tom Li - MSFT Thursday, June 24, 2010 10:52 AM
    • Marked as answer by Tom Li - MSFT Sunday, June 27, 2010 9:55 AM
    Tuesday, June 22, 2010 10:47 PM

All replies

  • Those are local windows groups that are used to manage SQL Server.  You can add the logins back by adding a windows group as the login, just like you would any other windows group.  Just select the groups from the local server and not the AD domain.
    "willo2010" wrote in message news:b649047b-7c4d-429b-b7f4-8fb47f476874...

    I deleted these groups from the SQL server logins by mistake.

    ServerName\SQLServer2005MSFTEUser$ServerName$InstanceName
    ServerName\SQLServer2005MSSQLUser$ServerName$InstanceName
    ServerName\SQLServer2005SQLAgentUser$ServerName$InstanceName.

    If anyone could point me towards directions on how to recreate these logins I would greatly appreciate it.

    • Proposed as answer by Tom Li - MSFT Thursday, June 24, 2010 10:53 AM
    • Marked as answer by Tom Li - MSFT Sunday, June 27, 2010 9:55 AM
    Tuesday, June 22, 2010 1:52 AM
  • Hi,

    If you have a backup copy of master database which has the deleted logins information (i.e. before deleting the server logins) you can follow the following steps.

    If your server is Production environment

    1. Take any test environment and copy the master databse .mdf and .ldf files to some other location.

    2. Up the test SQL server services and restore the master database backup file.

    3. Generate the login script on test server. (How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008 : http://support.microsoft.com/kb/918992/en-us)

    4. took the create login statements for the dropped logins.

    5. run that login script on prod server.

    6. execute the sp_change_users_login SP on all databases to fix the orphaned logins, if any.

    If your server is Dev environment you can try the above steps on the same server.

    --------------------------

    Thanks,

    RajaSekhar Reddy .K

     

    • Proposed as answer by Tom Li - MSFT Thursday, June 24, 2010 10:52 AM
    • Marked as answer by Tom Li - MSFT Sunday, June 27, 2010 9:55 AM
    Tuesday, June 22, 2010 6:02 AM
  • I deleted these groups from the SQL server logins by mistake.

    ServerName\SQLServer2005MSFTEUser$ServerName$InstanceName
    ServerName\SQLServer2005MSSQLUser$ServerName$InstanceName
    ServerName\SQLServer2005SQLAgentUser$ServerName$InstanceName.

    If anyone could point me towards directions on how to recreate these logins I would greatly appreciate it.


    It is that simple , execute the below scripts and assign necessary privileges .

     

    /****** Object:  Login [MACHINENAME\SQLServer2005SQLAgentUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:12 ******/

    /****** Object:  Login [MACHINENAME\SQLServer2005SQLAgentUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:12 ******/

    CREATE LOGIN [MACHINENAME\SQLServer2005SQLAgentUser$MACHINENAME$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

    /****** Object:  Login [MACHINENAME\SQLServer2005MSSQLUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:09 ******/

    /****** Object:  Login [MACHINENAME\SQLServer2005MSSQLUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:45:09 ******/

    CREATE LOGIN [MACHINENAME\SQLServer2005MSSQLUser$MACHINENAME$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

    /****** Object:  Login [MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:44:44 ******/

    /****** Object:  Login [MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER]    Script Date: 06/23/2010 10:44:44 ******/

    CREATE LOGIN [MACHINENAME\SQLServer2005MSFTEUser$MACHINENAME$MSSQLSERVER] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]


    Thanks, Leks

    Replace the MACHINENAME with your local system name

    • Proposed as answer by Tom Li - MSFT Thursday, June 24, 2010 10:52 AM
    • Marked as answer by Tom Li - MSFT Sunday, June 27, 2010 9:55 AM
    Tuesday, June 22, 2010 10:47 PM
  • Hi Leks,

    will the Login SIDs does not change, if we manually create the logins?

    Thanks,

    RajaSekhar Reddy . K

    Wednesday, June 23, 2010 4:22 AM
  • The SID will not change in the case of Domain / windows accounts , no matter how many times you delete and add them to the SQL server.  You can try doing this by yourself.


    Thanks, Leks
    Wednesday, June 23, 2010 9:36 PM