Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Exporting Logins/Passwords

Status
Not open for further replies.
Eddiefdz

Eddiefdz

IS-IT--Management
Mar 20, 2002
273
US
Hello,

I need to find an easy way to export all the logins/passwords for my sql server. Is there a script that will pull that data, or is there some way to extract the information so that i can take it to another box?

Thanks,
Eddie

Eddie Fernandez
CCNA, Network+, A+, MCP
 
Sort by date Sort by votes
This FAQ that I wrote will give you the script to move them.

faq962-6608

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
You say that this script will actually create other scripts correct? And then all i should have to do is run the scripts on the new server right?

Thanks,
Eddie

Eddie Fernandez
CCNA, Network+, A+, MCP
 
Upvote 0 Downvote
That is correct. When you run sp_help_revlogin the output is a script for you to run on another server. It will create your logins with the same username, SID and password.


- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Hey,

Can you check the syntax on those, none of them seem to work for me it outputs some bad stuff:

Server: Msg 170, Level 15, State 1, Procedure sp_help_revlogin, Line 34
Line 34: Incorrect syntax near '
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT '.
Server: Msg 170, Level 15, State 1, Procedure sp_help_revlogin, Line 50
Line 50: Incorrect syntax near '+'.
Server: Msg 170, Level 15, State 1, Procedure sp_help_revlogin, Line 75
Line 75: Incorrect syntax near 'skip_encryption_old'.
Server: Msg 170, Level 15, State 1, Procedure sp_help_revlogin, Line 77
Line 77: Incorrect syntax near 'skip_encryption'.
Server: Msg 156, Level 15, State 1, Procedure sp_help_revlogin, Line 80
Incorrect syntax near the keyword 'END'.
Server: Msg 156, Level 15, State 1, Procedure sp_help_revlogin, Line 82
Incorrect syntax near the keyword 'END'.

I am using the one for 2000.

Thanks,
Eddie

Eddie Fernandez
CCNA, Network+, A+, MCP
 
Upvote 0 Downvote
Does this work.
Code: 
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

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Thanks for finding that. I've fixed the faq. Here is the new code.
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
 ----- End Script -----

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
ok,

well this is what i get now:

The command(s) completed successfully.


So where is the output that I am supposed to get?

Thanks,
Eddie

Eddie Fernandez
CCNA, Network+, A+, MCP
 
Upvote 0 Downvote
The first time you run it, it will create the stored procedure. Now you have to type.

Code: 
exec master..sp_help_revlogin

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
did you get this to work for you?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Yes, it worked! Thanks.. Now i have another question. It does not seem to copy the rights. Is there any way of doing that? This just copy the actual user/password but he rights that the user has are not copied over.

Thanks,
Eddie

Eddie Fernandez
CCNA, Network+, A+, MCP
 
Upvote 0 Downvote
Is this for 2000 or 2005?
Here is a link that will work for 2005, You have to change the tables for 2000. The 2000 tables are syspermissions, sysprotects, sysobjects, sysusers, syscolumns


- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

simian336
  • Locked
  • Question
Logins Per Second
Replies
0
Views
63
simian336
simian336
bvuong
  • Locked
  • Question
Passwords not changing
Replies
3
Views
69
gmmastros
gmmastros
wbodger
  • Locked
  • Question
Upgrade this weekend, need to determine Logins
Replies
0
Views
40
wbodger
wbodger
wbodger
  • Locked
  • Question
exporting a set of databasees for public use
Replies
2
Views
42
wbodger
wbodger
kermitforney
  • Locked
  • Question
SQL Server Security - Logins and Groups
Replies
2
Views
31
mrdenny
mrdenny

Part and Inventory Search

Sponsor

Back
Top