Results 1 to 3 of 3

Thread: SQL 7 Users

  1. #1
    Join Date
    Aug 2004
    Location
    London
    Posts
    6

    Unanswered: SQL 7 Users

    Hiya,

    I need to get a copy of the users table from the master DB to another machine I have.

    This is to include permissions / passwords etc

    anyway this can be done? Cheers!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It isn't a good idea to allow users to create tables in the master database. Moving those tables to another database is a very good idea.

    My first suggestion would be to script the tables that belong to users out of the master database using SQL Enterprise Manager (to get the permisisons and any indexes and foreign keys), then play that script into your other database using SQL Query Analyzer. You may want to check for any constraints, since those might force you to move data into the tables in a particular order.

    Once the tables are created, I'd suggest that you move the data from one server/database to the other using the DTS wizard.

    There are other ways to do this. These are not the tools that I'd use myself, but I think these tools are a lot easier for the less experienced person. I would use this general strategy, but I'd use simpler, more "primative" tools to get the job done (when it comes to databases, I'm a speed junkie as well as being a lazy bum).

    -PatP

  3. #3
    Join Date
    Dec 2004
    Location
    York, PA
    Posts
    95
    This creates a procedure that you will need to run

    Code:
    USE master
    GO
    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
    
    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
    Sorry to be terse
    some say it's a curse
    I know it's worse
    I'm just diverse

Posting Permissions

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