"Greg D. Moore \(Strider\)" <mo****************@greenms.com> wrote in message news:<7J********************@twister.nyroc.rr.com> ...
"SD" <np****@nowhere.net> wrote in message
news:13******************************@news.teranew s.com... Hi,
Quick question, I have about 20 users in my local server and database. We
are looking to restore that database to a new server in a new network and
still be able to retain the database users.
We have created all 20 logins in the new server (exact names) but when we
restore the database I do not see the users anymore!
sp_change_users_login is probably what you need.
Is there something we can do to preserve them or a script we can run to
link them again?
Thanks a bunch!!!
S
You can run the following script to generate a create script for all
of your users. It will generate a create statement for each login
using the encrypted password and SID. Replace "MY_DB" in the script
with your database name, then run the script against your source
database. Copy the output of the script to your destination server and
run it. Then, when you restore your database, your users will
automatically be synched with their logins. Hope this helps!
Lisa
--------------
--Description: Scripts logins for one db
--*****IMPORTANT replace MY_DB with the name of the database that
holds the users that you need scripted
----- Begin Script, Create sp_help_revlogin procedure -----
USE master
GO
SET NOCOUNT ON
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_MY_DBLogins @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 a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT a.sid, a.name, a.xstatus, a.password FROM
master..sysxlogins a
join MY_DB..sysusers MY_DB on a.sid = MY_DB.sid
WHERE a.srvid IS NULL AND a.name <> 'sa'
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
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
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 -----
--
EXEC master..sp_help_MY_DBLogins
--drop proc sp_help_MY_DBLogins
