Loading ...
Sorry, an error occurred while loading the content.

ONE MORE SOLUTION: [mugh-sqlcon] List of DB Logind, users and their roles..

Expand Messages
  • veer wangoo
    One more way Go to DTS and you ahve migrate Logins Utility..Im wanted to show the snapshot and found it her at
    Message 1 of 6 , May 9, 2005
    • 0 Attachment

      One more way

       

      Go to DTS and you ahve migrate Logins Utility..Im wanted to show the snapshot and found it her at

       

      http://www.sqlservercentral.com/columnists/sjones/migratingtoanewservermovinglogins.asp

       

      Hope it solves the problem

       


      Do you Yahoo!?
      Yahoo! Mail - Find what you need with new enhanced search. Learn more.
    • sqlindia
      Hi Veer Thanx a lot for your quick response... I have been through these article on sqlservercentral and sqlteam website..I think these simple little
      Message 2 of 6 , May 9, 2005
      • 0 Attachment
        Hi Veer

        Thanx a lot for your quick response... I have been through these
        article on sqlservercentral and sqlteam website..I think these simple
        little complicated flor me at this stage..

        I just give you scenario. for instance

        1. one user/login account with password
        2. 2 roles which assign to this user..

        I would like to extract them into table and then read the table create
        (using sp_addlogin and other available SPs) them into another database..

        I have the structure in my mind like as follows...

        1. connect to db..
        2. create temp table
        3. declare cursor and execute the statement to pick them up the one
        not exist in second database and insert them into temp table..
        4. then read the table and create then in second database..

        I require help at stage 3. I hope this is more clear what I am looking
        for..

        regards
        sqlindia


        --- In sqlcon@yahoogroups.com, veer wangoo <vwangoo@y...> wrote:
        > One more way
        >
        >
        >
        > Go to DTS and you ahve migrate Logins Utility..Im wanted to show the
        snapshot and found it her at
        >
        >
        >
        >
        http://www.sqlservercentral.com/columnists/sjones/migratingtoanewservermovinglogins.asp
        >
        >
        >
        > Hope it solves the problem
        >
        >
        >
        >
        > ---------------------------------
        > Do you Yahoo!?
        > Yahoo! Mail - Find what you need with new enhanced search. Learn more.
      • veer wangoo
        /* Dear SQL INDIA, As a matter of fact you have to synchronise the logins between the two servers..Right It is beautiful test case but before automating it you
        Message 3 of 6 , May 9, 2005
        • 0 Attachment

          /* Dear SQL INDIA,

          As a matter of fact you have to synchronise the logins between the two servers..Right

          It is beautiful test case but before automating it you need to go step by step manually

          Strategy

          1. Take the Logins out of one server and generate the Users

          2. Cross map them and if there are duplicates, Just use only those of the source Database.and drop the destination logins

          3. So follow the Code in the below two SPs and tweak it if required...

          4. One more question are you building any application for this purpose or You are using Production Environment???

          OKAY START FROM HERE 

          Even if you do part 3 later on but you need to carry out passwords for SQl Authenticated Logins to other server so please follow the below Procedure  .

           

          */

          USE master
          GO

          --1. Make this Stored procesdure

          -- SCRIPT to Change Hexadecimal Passwords into the Sql encryption and ready for transfer

           

          IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
          DROP PROCEDURE sp_hexadecimal
          GO
          CREATE PROCEDURE sp_hexadecimal
          @binvalue varbinary(256),
          @hexvalue varchar(256) OUTPUT
          AS
          DECLARE @charvalue varchar(256)
          DECLARE @i int
          DECLARE @length int
          DECLARE @hexstring char(16)
          SELECT @charvalue = '0x'
          SELECT @i = 1
          SELECT @length = DATALENGTH (@binvalue)
          SELECT @hexstring = '0123456789ABCDEF'
          WHILE (@i <= @length)
          BEGIN
          DECLARE @tempint int
          DECLARE @firstint int
          DECLARE @secondint int
          SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
          SELECT @firstint = FLOOR(@tempint/16)
          SELECT @secondint = @tempint - (@firstint*16)
          SELECT @charvalue = @charvalue +
          SUBSTRING(@hexstring, @firstint+1, 1) +
          SUBSTRING(@hexstring, @secondint+1, 1)
          SELECT @i = @i + 1
          END
          SELECT @hexvalue = @charvalue
          GO

          ---- Next make this Stored procedure on the source Server , Now once your password

          --- decryptor / carrier is ready then you need to loop through the logins and check what are the --- logins on the other server...

          IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
          DROP PROCEDURE sp_help_revlogin
          GO
          CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
          DECLARE @name sysname
          DECLARE @xstatus int
          DECLARE @binpwd varbinary (256)
          DECLARE @txtpwd sysname
          DECLARE @tmpstr varchar (256)
          DECLARE @SID_varbinary varbinary(85)
          DECLARE @SID_string varchar(256)

          IF (@login_name IS NULL)
          DECLARE login_curs CURSOR FOR
          SELECT sid, name, xstatus, password FROM master..sysxlogins
          WHERE srvid IS NULL AND name <> 'sa'
          ELSE
          DECLARE login_curs CURSOR FOR
          SELECT sid, name, xstatus, password FROM master..sysxlogins
          WHERE srvid IS NULL AND name = @login_name
          OPEN login_curs
          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @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 (@xstatus & 4) = 4
          BEGIN -- NT authenticated account/group
          IF (@xstatus & 1) = 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
          IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
          ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
          PRINT @tmpstr
          EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
          SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
          END
          ELSE BEGIN
          -- Null password
          EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
          SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
          END
          IF (@xstatus & 2048) = 2048
          -- login upgraded from 6.5
          SET @tmpstr = @tmpstr + '''skip_encryption_old'''
          ELSE
          SET @tmpstr = @tmpstr + '''skip_encryption'''
          PRINT @tmpstr
          END
          END
          FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
          END
          CLOSE login_curs
          DEALLOCATE login_curs
          RETURN 0
          GO
          ----- End Script -----


          3. once you have created the two Stored procs ..Now you can use them any way u like

          --All you need to do is run

          EXEC master..sp_help_revlogin
          --- and save the out put

          --on source Server and run it on destination Server and if there are already user or logins then it will show errors for those...U can write some code to catch the Error and rectify accordinlgy..


          --4 THe output you get.... needs to be saved on .sql and run on the destination Server.
          --It migrates even the Password ..it is so Awesome..Even the Cursor that you asked is in it..you just need to break down the code a bit

           

           



          VEER JI WANGOO
          SQL freak...Dot Net Charmer

          ALTERNAT EMAIL ID:- veer_w@...

          ONLINE
          http://vsql.blogspot.com/
          www.mugh.net
          www.geekswithblogs.net/veerji

          HOME LAND IS NOT A MIRAGE BUT OUR DESTINY.
          WE WILL MAKE IT HAPPEN BEFORE WE BREATHE OUR LAST.


          Do you Yahoo!?
          Yahoo! Mail - Find what you need with new enhanced search. Learn more.
        • sqlindia
          Hi veer Thanx for your help.. Here it is i have developed script but i still need help on last bit where nee to read the table and add users, roles and add
          Message 4 of 6 , May 10, 2005
          • 0 Attachment
            Hi veer

            Thanx for your help.. Here it is i have developed script but i still
            need help on last bit where nee to read the table and add users, roles
            and add users into roles on target database..

            script......................
            CREATE Procedure USP_Roles_Objects

            AS

            DECLARE @sql nvarchar(4000)
            DECLARE @SQL_Insert nvarchar(4000)
            DECLARE @USQL nvarchar(4000)
            DECLARE @sourcedb varchar(100)
            DECLARE @targetdb varchar(100)
            DECLARE @targetchkdb varchar(100)
            DECLARE @basedb varchar(100)
            DECLARE @dbname varchar(100)
            DECLARE @uname varchar(100)
            DECLARE @rname varchar(100)
            DECLARE @fname varchar(100)
            DECLARE @fsoHandle int, @fileID int
            DECLARE @usedb nvarchar(100)
            DECLARE @adrole nvarchar(200)
            DECLARE @aduser nvarchar(200)
            DECLARE @adlogin nvarchar(200)
            DECLARE @adrolemem nvarchar(200)
            DECLARE @tware nvarchar(200)
            DECLARE @loginuser nvarchar(100)
            DECLARE @password nvarchar(100)
            DECLARE @role1 nvarchar(100)
            DECLARE @role2 nvarchar(100)


            SET @sourcedb = 'sourcedb'
            SET @targetdb = 'targetdb'
            SET @basedb = 'wrkdb'

            SET nocount on
            if exists (select * from dbo.sysobjects where id =
            object_id(N'[Roles_Objects]') and OBJECTPROPERTY(id, N'IsUserTable')
            = 1)
            TRUNCATE TABLE [Roles_Objects]

            IF exists (SELECT [id] FROM tempdb..sysobjects WHERE [id] =
            OBJECT_ID ('tempdb..#USRTABLE'))
            DROP TABLE #USRTABLE

            CREATE TABLE #USRTABLE
            (DatabaseName sysname NOT NULL,
            UserName sysname NOT NULL,
            RoleName sysname NOT NULL)

            SELECT @SQL =
            'INSERT INTO #USRTABLE
            SELECT N'''+ @targetdb +''', a.name AS UserName, c.name AS Roles
            FROM ' + (@sourcedb) + '.dbo.sysusers a INNER JOIN '
            + (@sourcedb) + '.dbo.sysmembers b ON a.uid =
            b.memberuid INNER JOIN '
            + (@sourcedb) + '.dbo.sysusers c ON b.groupuid
            = c.uid
            WHERE (c.name <> ''db_owner'')
            AND (NOT EXISTS (SELECT a.name AS UserName, c.name AS Roles
            FROM ' + (@targetdb) + '.dbo.sysusers a
            INNER JOIN '
            + (@targetdb) + '.dbo.sysmembers b ON b.memberuid = a.uid INNER
            JOIN '
            + (@targetdb) + '.dbo.sysusers c ON c.uid = b.groupuid
            WHERE c.name <> ''db_owner''))'

            /* Insert row for each database */
            EXECUTE sp_executesql @SQL
            SET @SQL = ''

            DECLARE cur_users cursor forward_only
            FOR SELECT DatabaseName,UserName,RoleName from #USRTABLE

            OPEN cur_users
            FETCH NEXT FROM cur_users INTO @dbname, @uname, @rname
            WHILE @@fetch_status = 0
            BEGIN
            INSERT INTO wrkdb.dbo.UserRoles_Objects
            (DBname,UserName,RoleName)
            values (@targetdb, @uname , @rname)
            FETCH NEXT FROM cur_users INTO @dbname, @uname, @rname
            END
            DROP TABLE #USRTABLE
            CLOSE cur_users
            DEALLOCATE cur_users


            /** Read the table and add Users, Roles and include users to the
            roles in Target Database**/


            ******* FROM here I need help **************
            IF EXISTS (SELECT * FROM wrkdb.dbo.UserRoles_Objects)
            SELECT @targetchkdb = DBname from wrkdb.dbo.UserRoles_Objects
            IF @targetchkdb = @targetdb
            SET @USQL= 'SELECT a.name
            FROM ' + (@targetdb) + '.dbo.sysusers a
            INNER JOIN '
            + (@targetdb) + '.dbo.sysmembers b ON b.memberuid = a.uid INNER
            JOIN '
            + (@targetdb) + '.dbo.sysusers c ON c.uid = b.groupuid
            WHERE c.name <> ''db_owner'''

            DECLARE cur_roles cursor forward_only
            FOR SELECT DBname,UserName,RoleName from wrkdb.dbo.UserRoles_Objects

            OPEN cur_roles
            FETCH NEXT FROM cur_roles INTO @dbname, @uname, @rname
            WHILE @@fetch_status = 0
            BEGIN
            SELECT @usedb = 'USE' +@dbname
            EXEC (@usedb)

            EXECUTE sp_executesql @USQL
            SET @adrole = 'EXEC sp_addrole ' +char(39)+@rname+char(39)
            EXECUTE sp_executesql @adrole

            --IF NOT EXISTS(@USQL)
            set @aduser = 'EXEC sp_adduser ' +char(39)+@uname+char(39)
            --print @aduser
            execute sp_executesql @aduser
            set @adrolemem = 'EXEC sp_addrolemember '
            +char(39)+@rname+char(39)+','+ char(39)+@uname+char(39)
            --print @adrolemem
            execute sp_executesql @adrolemem

            SET @adrole =' '
            SET @adrolemem =' '
            FETCH NEXT FROM cur_roles INTO @dbname, @uname, @rname
            END
            GO
            --------------------------------------------------------------------
            regards





            --- In sqlcon@yahoogroups.com, veer wangoo <vwangoo@y...> wrote:
            >
            > /* Dear SQL INDIA,
            >
            > As a matter of fact you have to synchronise the logins between the
            two servers..Right
            >
            > It is beautiful test case but before automating it you need to go
            step by step manually
            >
            > Strategy
            >
            > 1. Take the Logins out of one server and generate the Users
            >
            > 2. Cross map them and if there are duplicates, Just use only those
            of the source Database.and drop the destination logins
            >
            > 3. So follow the Code in the below two SPs and tweak it if
            required...
            >
            > 4. One more question are you building any application for this
            purpose or You are using Production Environment???
            >
            > OKAY START FROM HERE
            >
            > Even if you do part 3 later on but you need to carry out passwords
            for SQl Authenticated Logins to other server so please follow the
            below Procedure .
            >
            >
            >
            > */
            >
            > USE master
            > GO
            >
            > --1. Make this Stored procesdure
            >
            > -- SCRIPT to Change Hexadecimal Passwords into the Sql encryption
            and ready for transfer
            >
            >
            >
            >
            > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
            > DROP PROCEDURE sp_hexadecimal
            > GO
            > CREATE PROCEDURE sp_hexadecimal
            > @binvalue varbinary(256),
            > @hexvalue varchar(256) OUTPUT
            > AS
            > DECLARE @charvalue varchar(256)
            > DECLARE @i int
            > DECLARE @length int
            > DECLARE @hexstring char(16)
            > SELECT @charvalue = '0x'
            > SELECT @i = 1
            > SELECT @length = DATALENGTH (@binvalue)
            > SELECT @hexstring = '0123456789ABCDEF'
            > WHILE (@i <= @length)
            > BEGIN
            > DECLARE @tempint int
            > DECLARE @firstint int
            > DECLARE @secondint int
            > SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
            > SELECT @firstint = FLOOR(@tempint/16)
            > SELECT @secondint = @tempint - (@firstint*16)
            > SELECT @charvalue = @charvalue +
            > SUBSTRING(@hexstring, @firstint+1, 1) +
            > SUBSTRING(@hexstring, @secondint+1, 1)
            > SELECT @i = @i + 1
            > END
            > SELECT @hexvalue = @charvalue
            > GO
            >
            > ---- Next make this Stored procedure on the source Server , Now once
            your password
            >
            > --- decryptor / carrier is ready then you need to loop through the
            logins and check what are the --- logins on the other server...
            >
            > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
            > DROP PROCEDURE sp_help_revlogin
            > GO
            > CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
            > DECLARE @name sysname
            > DECLARE @xstatus int
            > DECLARE @binpwd varbinary (256)
            > DECLARE @txtpwd sysname
            > DECLARE @tmpstr varchar (256)
            > DECLARE @SID_varbinary varbinary(85)
            > DECLARE @SID_string varchar(256)
            >
            > IF (@login_name IS NULL)
            > DECLARE login_curs CURSOR FOR
            > SELECT sid, name, xstatus, password FROM master..sysxlogins
            > WHERE srvid IS NULL AND name <> 'sa'
            > ELSE
            > DECLARE login_curs CURSOR FOR
            > SELECT sid, name, xstatus, password FROM master..sysxlogins
            > WHERE srvid IS NULL AND name = @login_name
            > OPEN login_curs
            > FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus,
            @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 (@xstatus & 4) = 4
            > BEGIN -- NT authenticated account/group
            > IF (@xstatus & 1) = 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
            > IF (@xstatus & 2048) = 2048
            > SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
            > ELSE
            > SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
            > PRINT @tmpstr
            > EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
            > SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
            > + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
            > END
            > ELSE BEGIN
            > -- Null password
            > EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
            > SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
            > + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
            > END
            > IF (@xstatus & 2048) = 2048
            > -- login upgraded from 6.5
            > SET @tmpstr = @tmpstr + '''skip_encryption_old'''
            > ELSE
            > SET @tmpstr = @tmpstr + '''skip_encryption'''
            > PRINT @tmpstr
            > END
            > END
            > FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus,
            @binpwd
            > END
            > CLOSE login_curs
            > DEALLOCATE login_curs
            > RETURN 0
            > GO
            > ----- End Script -----
            >
            >
            > 3. once you have created the two Stored procs ..Now you can use them
            any way u like
            >
            > --All you need to do is run
            >
            > EXEC master..sp_help_revlogin
            > --- and save the out put
            >
            > --on source Server and run it on destination Server and if there are
            already user or logins then it will show errors for those...U can
            write some code to catch the Error and rectify accordinlgy..
            >
            >
            > --4 THe output you get.... needs to be saved on .sql and run on the
            destination Server.
            > --It migrates even the Password ..it is so Awesome..Even the Cursor
            that you asked is in it..you just need to break down the code a bit
            >
            >
            >
            >
            >
            >
            > VEER JI WANGOO
            > SQL freak...Dot Net Charmer
            >
            > ALTERNAT EMAIL ID:- veer_w@h...
            >
            > ONLINE
            > http://vsql.blogspot.com/
            > www.mugh.net
            > www.geekswithblogs.net/veerji
            >
            > HOME LAND IS NOT A MIRAGE BUT OUR DESTINY.
            > WE WILL MAKE IT HAPPEN BEFORE WE BREATHE OUR LAST.
            >
            > ---------------------------------
            > Do you Yahoo!?
            > Yahoo! Mail - Find what you need with new enhanced search. Learn
            more.
          • veer wangoo
            Beleive me it is working out to be a good script... I am at work right now..But tonight I will set up two SQl Instances at home and make it work.... It would
            Message 5 of 6 , May 10, 2005
            • 0 Attachment
              Beleive me it is working out to be a good script...
               
              I am at work right now..But tonight I will set up two SQl Instances at home and make it work....
               
              It would be fantastic if we can ship across Roles along with User/Login/Pwds ...Because till now we have mostly done partial steps manuallya nd partial through Automated Script...
               
              I request others two jumpo in as well.. ...
               
              Regards
              Veeru

              sqlindia <sqlindia@...> wrote:
              Hi veer

              Thanx for your help.. Here it is i have developed script but i still
              need help on last bit where nee to read the table and add users, roles
              and add users into roles on target database..

              script......................
              CREATE Procedure USP_Roles_Objects

              AS

              DECLARE @sql nvarchar(4000)
              DECLARE @SQL_Insert nvarchar(4000)
              DECLARE @USQL nvarchar(4000)
              DECLARE @sourcedb varchar(100)
              DECLARE @targetdb varchar(100)
              DECLARE @targetchkdb varchar(100)
              DECLARE @basedb varchar(100)
              DECLARE @dbname varchar(100)
              DECLARE @uname varchar(100)
              DECLARE @rname varchar(100)
              DECLARE @fname varchar(100)
              DECLARE @fsoHandle int, @fileID int
              DECLARE @usedb nvarchar(100)
              DECLARE @adrole nvarchar(200)
              DECLARE @aduser nvarchar(200)
              DECLARE @adlogin nvarchar(200)
              DECLARE @adrolemem nvarchar(200)
              DECLARE @tware nvarchar(200)
              DECLARE @loginuser nvarchar(100)
              DECLARE @password nvarchar(100)
              DECLARE @role1 nvarchar(100)
              DECLARE @role2 nvarchar(100)


              SET @sourcedb = 'sourcedb'
              SET @targetdb = 'targetdb'
              SET @basedb = 'wrkdb'

              SET nocount on
              if exists (select * from dbo.sysobjects where id =
              object_id(N'[Roles_Objects]') and OBJECTPROPERTY(id, N'IsUserTable')
              = 1)
              TRUNCATE TABLE [Roles_Objects]

              IF exists (SELECT [id] FROM tempdb..sysobjects WHERE [id] =
              OBJECT_ID ('tempdb..#USRTABLE'))                             
                    DROP TABLE #USRTABLE                       

              CREATE TABLE #USRTABLE
                    (DatabaseName sysname NOT NULL,                       
                      UserName sysname NOT NULL,                       
                      RoleName sysname NOT NULL)                       
                               
                    SELECT @SQL =            
                    'INSERT INTO #USRTABLE     
                    SELECT N'''+ @targetdb +''', a.name AS UserName, c.name AS Roles
                    FROM ' + (@sourcedb) + '.dbo.sysusers a INNER JOIN '
                                          + (@sourcedb) + '.dbo.sysmembers b ON a.uid =
              b.memberuid INNER JOIN '
                                          + (@sourcedb) + '.dbo.sysusers c ON b.groupuid
              = c.uid
                    WHERE (c.name <> ''db_owner'') 
                    AND (NOT EXISTS (SELECT  a.name AS UserName, c.name AS Roles
                                          FROM ' + (@targetdb) + '.dbo.sysusers a
              INNER JOIN '
                              + (@targetdb) + '.dbo.sysmembers b ON b.memberuid = a.uid INNER
              JOIN '
                              + (@targetdb) + '.dbo.sysusers c ON c.uid = b.groupuid
                                          WHERE  c.name <> ''db_owner''))'

                          /*       Insert row for each database */
                    EXECUTE sp_executesql @SQL
                    SET @SQL = ''

              DECLARE cur_users cursor forward_only
              FOR SELECT DatabaseName,UserName,RoleName from #USRTABLE

              OPEN cur_users
              FETCH NEXT FROM cur_users INTO @dbname, @uname, @rname
              WHILE @@fetch_status = 0
              BEGIN
                    INSERT INTO wrkdb.dbo.UserRoles_Objects
              (DBname,UserName,RoleName)
                                values (@targetdb, @uname , @rname)     
                    FETCH NEXT FROM cur_users INTO  @dbname, @uname, @rname
              END
              DROP TABLE #USRTABLE
              CLOSE cur_users
              DEALLOCATE cur_users


              /** Read the table and add Users, Roles and include users to the
              roles in Target Database**/


              *******  FROM here I need help **************
              IF EXISTS (SELECT * FROM wrkdb.dbo.UserRoles_Objects)
              SELECT @targetchkdb = DBname from wrkdb.dbo.UserRoles_Objects
              IF @targetchkdb = @targetdb
              SET @USQL= 'SELECT  a.name
                                          FROM ' + (@targetdb) + '.dbo.sysusers a
              INNER JOIN '
                              + (@targetdb) + '.dbo.sysmembers b ON b.memberuid = a.uid INNER
              JOIN '
                              + (@targetdb) + '.dbo.sysusers c ON c.uid = b.groupuid
                                          WHERE  c.name <> ''db_owner'''

                    DECLARE cur_roles cursor forward_only
                    FOR SELECT DBname,UserName,RoleName from wrkdb.dbo.UserRoles_Objects

                    OPEN cur_roles
                    FETCH NEXT FROM cur_roles INTO @dbname, @uname, @rname
                    WHILE @@fetch_status = 0
                    BEGIN
                          SELECT @usedb = 'USE' +@dbname
                          EXEC (@usedb)

                          EXECUTE sp_executesql @USQL
                          SET @adrole = 'EXEC sp_addrole ' +char(39)+@rname+char(39)
                          EXECUTE sp_executesql @adrole

                          --IF NOT EXISTS(@USQL)
                          set @aduser = 'EXEC sp_adduser ' +char(39)+@uname+char(39)
                          --print @aduser
                          execute sp_executesql @aduser
                          set @adrolemem = 'EXEC sp_addrolemember '
              +char(39)+@rname+char(39)+','+ char(39)+@uname+char(39)
                          --print @adrolemem
                          execute sp_executesql @adrolemem

                          SET @adrole =' '
                          SET @adrolemem =' '
                          FETCH NEXT FROM cur_roles INTO  @dbname, @uname, @rname
                    END
              GO
              --------------------------------------------------------------------
              regards





              --- In sqlcon@yahoogroups.com, veer wangoo <vwangoo@y...> wrote:
              >
              > /* Dear SQL INDIA,
              >
              > As a matter of fact you have to synchronise the logins between the
              two servers..Right
              >
              > It is beautiful test case but before automating it you need to go
              step by step manually
              >
              > Strategy
              >
              > 1. Take the Logins out of one server and generate the Users
              >
              > 2. Cross map them and if there are duplicates, Just use only those
              of the source Database.and drop the destination logins
              >
              > 3. So follow the Code in the below two SPs and tweak it if
              required...
              >
              > 4. One more question are you building any application for this
              purpose or You are using Production Environment???
              >
              > OKAY START FROM HERE
              >
              > Even if you do part 3 later on but you need to carry out passwords
              for SQl Authenticated Logins to other server so please follow the
              below Procedure  .
              >

              >
              > */
              >
              > USE master
              > GO
              >
              > --1. Make this Stored procesdure
              >
              > -- SCRIPT to Change Hexadecimal Passwords into the Sql encryption
              and ready for transfer
              >
              >

              >
              > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
              > DROP PROCEDURE sp_hexadecimal
              > GO
              > CREATE PROCEDURE sp_hexadecimal
              > @binvalue varbinary(256),
              > @hexvalue varchar(256) OUTPUT
              > AS
              > DECLARE @charvalue varchar(256)
              > DECLARE @i int
              > DECLARE @length int
              > DECLARE @hexstring char(16)
              > SELECT @charvalue = '0x'
              > SELECT @i = 1
              > SELECT @length = DATALENGTH (@binvalue)
              > SELECT @hexstring = '0123456789ABCDEF'
              > WHILE (@i <= @length)
              > BEGIN
              > DECLARE @tempint int
              > DECLARE @firstint int
              > DECLARE @secondint int
              > SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
              > SELECT @firstint = FLOOR(@tempint/16)
              > SELECT @secondint = @tempint - (@firstint*16)
              > SELECT @charvalue = @charvalue +
              > SUBSTRING(@hexstring, @firstint+1, 1) +
              > SUBSTRING(@hexstring, @secondint+1, 1)
              > SELECT @i = @i + 1
              > END
              > SELECT @hexvalue = @charvalue
              > GO
              >
              > ---- Next make this Stored procedure on the source Server , Now once
              your password
              >
              > --- decryptor / carrier is ready then you need to loop through the
              logins and check what are the --- logins on the other server...
              >
              > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
              > DROP PROCEDURE sp_help_revlogin
              > GO
              > CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
              > DECLARE @name sysname
              > DECLARE @xstatus int
              > DECLARE @binpwd varbinary (256)
              > DECLARE @txtpwd sysname
              > DECLARE @tmpstr varchar (256)
              > DECLARE @SID_varbinary varbinary(85)
              > DECLARE @SID_string varchar(256)
              >
              > IF (@login_name IS NULL)
              > DECLARE login_curs CURSOR FOR
              > SELECT sid, name, xstatus, password FROM master..sysxlogins
              > WHERE srvid IS NULL AND name <> 'sa'
              > ELSE
              > DECLARE login_curs CURSOR FOR
              > SELECT sid, name, xstatus, password FROM master..sysxlogins
              > WHERE srvid IS NULL AND name = @login_name
              > OPEN login_curs
              > FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus,
              @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 (@xstatus & 4) = 4
              > BEGIN -- NT authenticated account/group
              > IF (@xstatus & 1) = 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
              > IF (@xstatus & 2048) = 2048
              > SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
              > ELSE
              > SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
              > PRINT @tmpstr
              > EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
              > SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
              > + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
              > END
              > ELSE BEGIN
              > -- Null password
              > EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
              > SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
              > + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
              > END
              > IF (@xstatus & 2048) = 2048
              > -- login upgraded from 6.5
              > SET @tmpstr = @tmpstr + '''skip_encryption_old'''
              > ELSE
              > SET @tmpstr = @tmpstr + '''skip_encryption'''
              > PRINT @tmpstr
              > END
              > END
              > FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus,
              @binpwd
              > END
              > CLOSE login_curs
              > DEALLOCATE login_curs
              > RETURN 0
              > GO
              > ----- End Script -----
              >
              >
              > 3. once you have created the two Stored procs ..Now you can use them
              any way u like
              >
              > --All you need to do is run
              >
              > EXEC master..sp_help_revlogin
              > --- and save the out put
              >
              > --on source Server and run it on destination Server and if there are
              already user or logins then it will show errors for those...U can
              write some code to catch the Error and rectify accordinlgy..
              >
              >
              > --4 THe output you get.... needs to be saved on .sql and run on the
              destination Server.
              > --It migrates even the Password ..it is so Awesome..Even the Cursor
              that you asked is in it..you just need to break down the code a bit
              >

              >

              >
              >
              > VEER JI WANGOO
              > SQL freak...Dot Net Charmer
              >
              > ALTERNAT EMAIL ID:- veer_w@h...
              >
              > ONLINE
              > http://vsql.blogspot.com/
              > www.mugh.net
              > www.geekswithblogs.net/veerji
              >
              > HOME LAND IS NOT A MIRAGE BUT OUR  DESTINY.
              > WE WILL MAKE IT HAPPEN BEFORE WE BREATHE OUR LAST.
              >            
              > ---------------------------------
              > Do you Yahoo!?
              >  Yahoo! Mail - Find what you need with new enhanced search. Learn
              more.






              SqlCon is a special interests group run under the aegis of MUGH - http://www.mugh.net
              http://www.mugh.net/sql

              Visit http://www.mugh.net/dnug to join .NET User Group.



              Do you Yahoo!?
              Make Yahoo! your home page

            • sqlindia
              Thanx Veer really appreciate it if this script going to be valuable script... please refer to previous linked post to get the whole scenario. Here it is little
              Message 6 of 6 , May 10, 2005
              • 0 Attachment
                Thanx Veer


                really appreciate it if this script going to be valuable script...
                please refer to previous linked post to get the whole scenario. Here
                it is little snapshot of scenario..

                I am implementing 2 roles and 1 User/Login..so 2 roles assign to 1
                user.. when i extract them into table there will be two records.. and
                then when we read them from table to create them into target database
                then we need to check if user (specifically user, cos user exist
                against both roles in both records in table) already created or not
                (well, ideally need to chack both users and roles any way) and then
                add sp_addrolemember part..

                I hope it will helpful....

                Regards
                sqlIndia



                --- In sqlcon@yahoogroups.com, veer wangoo <vwangoo@y...> wrote:
                > Beleive me it is working out to be a good script...
                >
                > I am at work right now..But tonight I will set up two SQl Instances
                at home and make it work....
                >
                > It would be fantastic if we can ship across Roles along with
                User/Login/Pwds ...Because till now we have mostly done partial steps
                manuallya nd partial through Automated Script...
                >
                > I request others two jumpo in as well.. ...
                >
                > Regards
                > Veeru
                >
                > sqlindia <sqlindia@y...> wrote:
                > Hi veer
                >
                > Thanx for your help.. Here it is i have developed script but i still
                > need help on last bit where nee to read the table and add users,
                roles
                > and add users into roles on target database..
                >
                > script......................
                > CREATE Procedure USP_Roles_Objects
                >
                > AS
                >
                > DECLARE @sql nvarchar(4000)
                > DECLARE @SQL_Insert nvarchar(4000)
                > DECLARE @USQL nvarchar(4000)
                > DECLARE @sourcedb varchar(100)
                > DECLARE @targetdb varchar(100)
                > DECLARE @targetchkdb varchar(100)
                > DECLARE @basedb varchar(100)
                > DECLARE @dbname varchar(100)
                > DECLARE @uname varchar(100)
                > DECLARE @rname varchar(100)
                > DECLARE @fname varchar(100)
                > DECLARE @fsoHandle int, @fileID int
                > DECLARE @usedb nvarchar(100)
                > DECLARE @adrole nvarchar(200)
                > DECLARE @aduser nvarchar(200)
                > DECLARE @adlogin nvarchar(200)
                > DECLARE @adrolemem nvarchar(200)
                > DECLARE @tware nvarchar(200)
                > DECLARE @loginuser nvarchar(100)
                > DECLARE @password nvarchar(100)
                > DECLARE @role1 nvarchar(100)
                > DECLARE @role2 nvarchar(100)
                >
                >
                > SET @sourcedb = 'sourcedb'
                > SET @targetdb = 'targetdb'
                > SET @basedb = 'wrkdb'
                >
                > SET nocount on
                > if exists (select * from dbo.sysobjects where id =
                > object_id(N'[Roles_Objects]') and OBJECTPROPERTY(id, N'IsUserTable')
                > = 1)
                > TRUNCATE TABLE [Roles_Objects]
                >
                > IF exists (SELECT [id] FROM tempdb..sysobjects WHERE [id] =
                > OBJECT_ID ('tempdb..#USRTABLE'))
                > DROP TABLE #USRTABLE
                >
                > CREATE TABLE #USRTABLE
                > (DatabaseName sysname NOT NULL,
                > UserName sysname NOT NULL,
                > RoleName sysname NOT NULL)
                >
                > SELECT @SQL =
                > 'INSERT INTO #USRTABLE
                > SELECT N'''+ @targetdb +''', a.name AS UserName, c.name AS
                Roles
                > FROM ' + (@sourcedb) + '.dbo.sysusers a INNER JOIN '
                > + (@sourcedb) + '.dbo.sysmembers b ON
                a.uid =
                > b.memberuid INNER JOIN '
                > + (@sourcedb) + '.dbo.sysusers c ON
                b.groupuid
                > = c.uid
                > WHERE (c.name <> ''db_owner'')
                > AND (NOT EXISTS (SELECT a.name AS UserName, c.name AS Roles
                > FROM ' + (@targetdb) + '.dbo.sysusers a
                > INNER JOIN '
                > + (@targetdb) + '.dbo.sysmembers b ON b.memberuid =
                a.uid INNER
                > JOIN '
                > + (@targetdb) + '.dbo.sysusers c ON c.uid =
                b.groupuid
                > WHERE c.name <> ''db_owner''))'
                >
                > /* Insert row for each database */
                > EXECUTE sp_executesql @SQL
                > SET @SQL = ''
                >
                > DECLARE cur_users cursor forward_only
                > FOR SELECT DatabaseName,UserName,RoleName from #USRTABLE
                >
                > OPEN cur_users
                > FETCH NEXT FROM cur_users INTO @dbname, @uname, @rname
                > WHILE @@fetch_status = 0
                > BEGIN
                > INSERT INTO wrkdb.dbo.UserRoles_Objects
                > (DBname,UserName,RoleName)
                > values (@targetdb, @uname , @rname)
                > FETCH NEXT FROM cur_users INTO @dbname, @uname, @rname
                > END
                > DROP TABLE #USRTABLE
                > CLOSE cur_users
                > DEALLOCATE cur_users
                >
                >
                > /** Read the table and add Users, Roles and include users to the
                > roles in Target Database**/
                >
                >
                > ******* FROM here I need help **************
                > IF EXISTS (SELECT * FROM wrkdb.dbo.UserRoles_Objects)
                > SELECT @targetchkdb = DBname from wrkdb.dbo.UserRoles_Objects
                > IF @targetchkdb = @targetdb
                > SET @USQL= 'SELECT a.name
                > FROM ' + (@targetdb) + '.dbo.sysusers a
                > INNER JOIN '
                > + (@targetdb) + '.dbo.sysmembers b ON b.memberuid =
                a.uid INNER
                > JOIN '
                > + (@targetdb) + '.dbo.sysusers c ON c.uid =
                b.groupuid
                > WHERE c.name <> ''db_owner'''
                >
                > DECLARE cur_roles cursor forward_only
                > FOR SELECT DBname,UserName,RoleName from
                wrkdb.dbo.UserRoles_Objects
                >
                > OPEN cur_roles
                > FETCH NEXT FROM cur_roles INTO @dbname, @uname, @rname
                > WHILE @@fetch_status = 0
                > BEGIN
                > SELECT @usedb = 'USE' +@dbname
                > EXEC (@usedb)
                >
                > EXECUTE sp_executesql @USQL
                > SET @adrole = 'EXEC sp_addrole ' +ch
                ar(39)+@rname+char(39)
                > EXECUTE sp_executesql @adrole
                >
                > --IF NOT EXISTS(@USQL)
                > set @aduser = 'EXEC sp_adduser ' +ch
                ar(39)+@uname+char(39)
                > --print @aduser
                > execute sp_executesql @aduser
                > set @adrolemem = 'EXEC sp_addrolemember '
                > +char(39)+@rname+char(39)+','+ char(39)+@uname+char(39)
                > --print @adrolemem
                > execute sp_executesql @adrolemem
                >
                > SET @adrole =' '
                > SET @adrolemem =' '
                > FETCH NEXT FROM cur_roles INTO @dbname, @uname, @rname
                > END
                > GO
                > --------------------------------------------------------------------
                > regards
                >
                >
                >
                >
                >
                > --- In sqlcon@yahoogroups.com, veer wangoo <vwangoo@y...> wrote:
                > >
                > > /* Dear SQL INDIA,
                > >
                > > As a matter of fact you have to synchronise the logins between the
                > two servers..Right
                > >
                > > It is beautiful test case but before automating it you need to go
                > step by step manually
                > >
                > > Strategy
                > >
                > > 1. Take the Logins out of one server and generate the Users
                > >
                > > 2. Cross map them and if there are duplicates, Just use only those
                > of the source Database.and drop the destination logins
                > >
                > > 3. So follow the Code in the below two SPs and tweak it if
                > required...
                > >
                > > 4. One more question are you building any application for this
                > purpose or You are using Production Environment???
                > >
                > > OKAY START FROM HERE
                > >
                > > Even if you do part 3 later on but you need to carry out passwords
                > for SQl Authenticated Logins to other server so please follow the
                > below Procedure .
                > >
                > >
                > >
                > > */
                > >
                > > USE master
                > > GO
                > >
                > > --1. Make this Stored procesdure
                > >
                > > -- SCRIPT to Change Hexadecimal Passwords into the Sql encryption
                > and ready for transfer
                > >
                > >
                > >
                > >
                > > IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
                > > DROP PROCEDURE sp_hexadecimal
                > > GO
                > > CREATE PROCEDURE sp_hexadecimal
                > > @binvalue varbinary(256),
                > > @hexvalue varchar(256) OUTPUT
                > > AS
                > > DECLARE @charvalue varchar(256)
                > > DECLARE @i int
                > > DECLARE @length int
                > > DECLARE @hexstring char(16)
                > > SELECT @charvalue = '0x'
                > > SELECT @i = 1
                > > SELECT @length = DATALENGTH (@binvalue)
                > > SELECT @hexstring = '0123456789ABCDEF'
                > > WHILE (@i <= @length)
                > > BEGIN
                > > DECLARE @tempint int
                > > DECLARE @firstint int
                > > DECLARE @secondint int
                > > SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
                > > SELECT @firstint = FLOOR(@tempint/16)
                > > SELECT @secondint = @tempint - (@firstint*16)
                > > SELECT @charvalue = @charvalue +
                > > SUBSTRING(@hexstring, @firstint+1, 1) +
                > > SUBSTRING(@hexstring, @secondint+1, 1)
                > > SELECT @i = @i + 1
                > > END
                > > SELECT @hexvalue = @charvalue
                > > GO
                > >
                > > ---- Next make this Stored procedure on the source Server , Now
                once
                > your password
                > >
                > > --- decryptor / carrier is ready then you need to loop through the
                > logins and check what are the --- logins on the other server...
                > >
                > > IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
                > > DROP PROCEDURE sp_help_revlogin
                > > GO
                > > CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
                > > DECLARE @name sysname
                > > DECLARE @xstatus int
                > > DECLARE @binpwd varbinary (256)
                > > DECLARE @txtpwd sysname
                > > DECLARE @tmpstr varchar (256)
                > > DECLARE @SID_varbinary varbinary(85)
                > > DECLARE @SID_string varchar(256)
                > >
                > > IF (@login_name IS NULL)
                > > DECLARE login_curs CURSOR FOR
                > > SELECT sid, name, xstatus, password FROM master..sysxlogins
                > > WHERE srvid IS NULL AND name <> 'sa'
                > > ELSE
                > > DECLARE login_curs CURSOR FOR
                > > SELECT sid, name, xstatus, password FROM master..sysxlogins
                > > WHERE srvid IS NULL AND name = @login_name
                > > OPEN login_curs
                > > FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus,
                > @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 (@xstatus & 4) = 4
                > > BEGIN -- NT authenticated account/group
                > > IF (@xstatus & 1) = 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
                > > IF (@xstatus & 2048) = 2048
                > > SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
                > > ELSE
                > > SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd +
                ')'
                > > PRINT @tmpstr
                > > EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
                > > SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
                > > + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
                > > END
                > > ELSE BEGIN
                > > -- Null password
                > > EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
                > > SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
                > > + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
                > > END
                > > IF (@xstatus & 2048) = 2048
                > > -- login upgraded from 6.5
                > > SET @tmpstr = @tmpstr + '''skip_encryption_old'''
                > > ELSE
                > > SET @tmpstr = @tmpstr + '''skip_encryption'''
                > > PRINT @tmpstr
                > > END
                > > END
                > > FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus,
                > @binpwd
                > > END
                > > CLOSE login_curs
                > > DEALLOCATE login_curs
                > > RETURN 0
                > > GO
                > > ----- End Script -----
                > >
                > >
                > > 3. once you have created the two Stored procs ..Now you can use
                them
                > any way u like
                > >
                > > --All you need to do is run
                > >
                > > EXEC master..sp_help_revlogin
                > > --- and save the out put
                > >
                > > --on source Server and run it on destination Server and if there
                are
                > already user or logins then it will show errors for those...U can
                > write some code to catch the Error and rectify accordinlgy..
                > >
                > >
                > > --4 THe output you get.... needs to be saved on .sql and run on
                the
                > destination Server.
                > > --It migrates even the Password ..it is so Awesome..Even the
                Cursor
                > that you asked is in it..you just need to break down the code a bit
                > >
                > >
                > >
                > >
                > >
                > >
                > > VEER JI WANGOO
                > > SQL freak...Dot Net Charmer
                > >
                > > ALTERNAT EMAIL ID:- veer_w@h...
                > >
                > > ONLINE
                > > http://vsql.blogspot.com/
                > > www.mugh.net
                > > www.geekswithblogs.net/veerji
                > >
                > > HOME LAND IS NOT A MIRAGE BUT OUR DESTINY.
                > > WE WILL MAKE IT HAPPEN BEFORE WE BREATHE OUR LAST.
                > >
                > > ---------------------------------
                > > Do you Yahoo!?
                > > Yahoo! Mail - Find what you need with new enhanced search. Learn
                > more.
                >
                >
                >
                >
                >
                >
                > SqlCon is a special interests group run under the aegis of MUGH -
                http://www.mugh.net
                > http://www.mugh.net/sql
                >
                > Visit http://www.mugh.net/dnug to join .NET User Group.
                >
                >
                >
                >
                > ---------------------------------
                > Yahoo! Groups Links
                >
                > To visit your group on the web, go to:
                > http://groups.yahoo.com/group/sqlcon/
                >
                > To unsubscribe from this group, send an email to:
                > sqlcon-unsubscribe@yahoogroups.com
                >
                > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                Service.
                >
                >
                >
                > ---------------------------------
                > Do you Yahoo!?
                > Make Yahoo! your home page
              Your message has been successfully submitted and would be delivered to recipients shortly.