Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2009
    Posts
    32

    Unanswered: Script to copy login from one server to another

    I would like to move a database from one server. In this move, I need a script to copy all logins only related to this database to the new environment. Does anyone have the script?
    The move can be from SQL 2000 to SQL 2000, SQL 2000 to SQL 2005, or SQL 2005 to SQL 2005.

    Any input will be greatly appreciated in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've never used any of these but kept a note in case I needed them:
    How to transfer SQL logins between SQL Server 2005 instances
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    use sp_helprevlogin


    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    -- =============================================

    -- Author: <Author,,Name>

    -- Create date: <Create Date,,>

    -- Description: <Description,,>

    -- =============================================

    Create PROCEDURE [dbo].[sp_Help_RevLogin2005]

    As

    DECLARE @name sysname

    DECLARE @IsNTName int

    DECLARE @denyLogin int

    DECLARE @binpwd varbinary(256)

    DECLARE @txtpwd nvarchar(128)

    DECLARE @tmpstr nvarchar (256)

    DECLARE @SID_varbinary varbinary(85)

    DECLARE @SID_string nvarchar(128)



    DECLARE login_curs CURSOR FOR

    SELECT sid, name, isNTName, denyLogin, convert(varbinary(256),password) as password FROM master..syslogins

    WHERE name <> 'sa' and hasAccess = 1

    OPEN login_curs

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @IsNTName, @DenyLogin, @binpwd

    IF (@@fetch_status = -1)

    BEGIN

    PRINT 'No login(s) found.'

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN -1

    END

    SET @tmpstr = '/* sp_help_revlogin script '

    PRINT @tmpstr

    SET @tmpstr = '** Generated '

    + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

    PRINT @tmpstr

    PRINT ''

    PRINT 'DECLARE @pwd sysname'

    WHILE (@@fetch_status <> -1)

    BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

    PRINT ''

    SET @tmpstr = '-- Login: ' + @name

    PRINT @tmpstr

    IF @IsNTName = 1

    BEGIN -- NT authenticated account/group

    IF @denyLogin = 1

    BEGIN -- NT login is denied access

    SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

    PRINT @tmpstr

    END

    ELSE BEGIN -- NT login has access

    SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

    PRINT @tmpstr

    END

    END

    ELSE BEGIN -- SQL Server authentication

    IF (@binpwd IS NOT NULL)

    BEGIN -- Non-null password

    EXEC sp_hexadecimal @binpwd, @txtpwd OUT

    SET @tmpstr = 'SET @pwd = ' + @txtPwd

    PRINT @tmpstr

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    SET @tmpstr = 'Create Login ' + @name + ' WITH PASSWORD=' + @txtpwd + ' HASHED, SID=' + @sid_string

    PRINT @tmpstr

    END

    ELSE BEGIN

    -- Null password

    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

    SET @tmpstr = 'Create Login ' + @name + ' WITH SID=' + @sid_string

    END

    END

    END

    FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @IsNTName, @DenyLogin, @binpwd

    END

    CLOSE login_curs

    DEALLOCATE login_curs

    RETURN 0

    exec sp_helprevlogin

  4. #4
    Join Date
    Mar 2009
    Posts
    32
    Thank you all!
    What I need is only the logins needs to access to the database to be moved. I have no intention to move all logins, which are not related to the database to be moved.
    Any more idea?
    Many thanks once again.

  5. #5
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    if you are in enterprise manager sql 2000 right click on the database, select tasks, generate sql script, then select the options tab, and choose the logins.

    If you are using sql server management studio 2005, right click on the database and select tasks, generate scripts, in the first tab under options change false to true to script sql server logins

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •