June 2, 2004 at 9:19 am
I am building a new sqlserver2000. How do I transfer all the logins
I tried 1. sp_configure 'allow updates',1
2.reconfigure with overirde
3. restored the old master to the new server with a different name
4. did a select * into tempdb..templogin from master_old..sysxlogins where name!='sa'
5. tried to copy to the sysxlogins table from templogin
It complains about column mismatch .How do I get around it?
Thanks
Ram
June 2, 2004 at 3:34 pm
Ram,
I have written a series of stored procedures to script out security for a server so that it can be transferred.
If all you are concerned about is the logins (not their permissions within the databases), that would involve 2 of my procs (one for the logins, one for the login attributes).
I'll post those here. If you want the other two (db users, and object level permissions), let me know and I'll post them as well.
The first one was not written by me, but by a former colleague of a colleague of mine. It includes two procedures sp_hexadecimal and af_revLogins. sp_hexadecimal should be put in the master database. af_revLogins can be put wherever you want it. I have a utility database called 'afDBA', which is where the default location for the 'af_' procs is. There is a statement in this script 'USE afDBA' before af_revLogins is created. You'll want to change that to your database of choice.
-- af_revLogins
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(255)
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
use afDBA
go
IF OBJECT_ID ('af_revLogins') IS NOT NULL
DROP PROCEDURE af_revLogins
GO
CREATE PROCEDURE af_revLogins @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (255)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (255)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* af_revLogins 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, ' + @txtpwd + ')'
ELSE
SET @tmpstr = 'SET @pwd = CONVERT (varbinary, ' + @txtpwd + ')'
PRINT @tmpstr
set @tmpstr = 'EXEC master..sp_addlogin ' + char(39) + @name + char(39)
+ ',
' + @txtpwd + ',
@encryptopt = '
END
ELSE BEGIN
-- Null password
SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
+ ''', NULL, @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 @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
June 2, 2004 at 3:36 pm
Here's the other one...
-- af_revLoginAttrib_2k
USE afDBA
-- Verify existence of sp_help_RevDBUsers procedure. If it does exist,
-- drop it, so that it can be re-created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[af_revLoginAttrib_2k]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'dropping existing af_revLoginAttrib_2k procedure.'
drop procedure [dbo].[af_revLoginAttrib_2k]
print 'creating af_revLoginAttrib_2k procedure.'
end
GO
create procedure af_revLoginAttrib_2k
as
/*************************************************************/
--
-- Module Name: af_revLoginAttrib_2k
--
-- Description:
-- The purpose of this procedure is to script the attributes
-- (default db, roles, etc) associated with the login-ids on a server.
--
-- Written By: Steve Phelps
--
-- Date: July 15, 2003
--
-- Modified : Steve Phelps
-- Date: 3/25/2004
-- Changed cursor to use loginname instead of name.
--
-- Modified :
-- Date:
--
--
-- USAGE:
--
-- exec af_revLoginAttrib_2k
--
/*************************************************************/
declare
@command varchar(500),
@name varchar(30),
@dbname varchar(128),
@DenyLogin int,
@HasAccess int,
@IsNTName int,
@IsNTGroup int,
@IsNTUser int,
@SysAdmin int,
@SecurityAdmin int,
@ServerAdmin int,
@SetupAdmin int,
@ProcessAdmin int,
@DiskAdmin int,
@DBCreator int,
@BulkAdmin int -- 2k only
declare login_cur cursor for
select
loginname, dbname, denylogin, hasaccess, isntname,
isntgroup, isntuser, sysadmin, securityadmin, serveradmin,
setupadmin, processadmin, diskadmin, dbcreator, bulkadmin
from master..syslogins
where loginname not in ('BUILTIN\Administrators', 'sa', 'SA')
order by loginname
open login_cur
fetch next from login_cur into
@name,
@dbname,
@DenyLogin,
@HasAccess,
@IsNTName,
@IsNTGroup,
@IsNTUser,
@SysAdmin,
@SecurityAdmin,
@ServerAdmin,
@SetupAdmin,
@ProcessAdmin,
@DiskAdmin,
@DBCreator,
@BulkAdmin
if (@@fetch_status = -1)
begin
close login_cur
deallocate login_cur
-- return -1
end
else
begin
while (@@fetch_status <> -1)
begin
if (@@fetch_status <> -2)
begin
-- begin processing current user.
print ' '
print '-- processing attributes for id = ' + @name
-- set the default database.
select @command = 'exec sp_defaultdb ' + char(39) + @name +
char(39) + ', ' + char(39) + @dbname + char(39)
print @command
-- add user to applicable roles.
if @SysAdmin = 1
BEGIN
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'sysadmin' + char(39)
print @command
END
else
if @SecurityAdmin = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'securityadmin' + char(39)
print @command
end
if @serveradmin = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'serveradmin' + char(39)
print @command
end
if @SetupAdmin = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'setupadmin' + char(39)
print @command
end
if @ProcessAdmin = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'processadmin' + char(39)
print @command
end
if @DiskAdmin = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'diskadmin' + char(39)
print @command
end
if @DBCreator = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'dbcreator' + char(39)
print @command
end
if @BulkAdmin = 1
begin
select @command = 'exec sp_addsrvrolemember ' +
char(39) + @name + char(39) + ', ' +
char(39) + 'bulkadmin' + char(39)
print @command
end
end -- if (@@fetch_status <> -2)
fetch next from login_cur into
@name,
@dbname,
@DenyLogin,
@HasAccess,
@IsNTName,
@IsNTGroup,
@IsNTUser,
@SysAdmin,
@SecurityAdmin,
@ServerAdmin,
@SetupAdmin,
@ProcessAdmin,
@DiskAdmin,
@DBCreator,
@BulkAdmin
end -- while (@@fetch_status <> -1)
close login_cur
deallocate login_cur
end -- (@@fetch_status = -1)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply