Login scripting to a file

I am new to DMO and I am trying to find a way where I can create a script file containing all server logins. THis needs to run on a daily basis.

Anyone have any examples to share?

Use the following procs, you will generate the login scripts.

Cut and paste the following proc and then execute

exec sp_script_generator 'c:\scripts\', <sa pwd>.

if sql server authentication, provide the password.

Please note that, I wrote the scripts long back.

If any issues in the procs, please post the same.

if exists (select * from sysobjects where id = object_id('dbo.sp_hexadecimal') and sysstat & 0xf = 4)
drop procedure dbo.sp_hexadecimal
GO

CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(255),
@.hexvalue varchar(255) 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

if exists (select * from sysobjects where id = object_id('dbo.sp_displayoaerrorinfo') and sysstat & 0xf = 4)
drop procedure dbo.sp_displayoaerrorinfo
GO

CREATE PROCEDURE sp_displayoaerrorinfo
@.object int,
@.hresult int
AS
DECLARE @.output varchar(255)
DECLARE @.hrhex char(10)
DECLARE @.hr int
DECLARE @.source varchar(255)
DECLARE @.description varchar(255)
PRINT 'OLE Automation Error Information'
EXEC sp_hexadecimal @.hresult, @.hrhex OUT
SELECT @.output = ' HRESULT: ' + @.hrhex
PRINT @.output
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
IF @.hr = 0
BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
END
ELSE
BEGIN
PRINT " sp_OAGetErrorInfo failed."
RETURN
END

GO

set quoted_identifier off
go
if exists (select * from sysobjects where id = object_id('dbo.sp_srv_obj_scr_gen') and sysstat & 0xf = 4)
drop procedure dbo.sp_srv_obj_scr_gen
GO

/****************************************************************************************************************/
/* Procedure : sp_srv_obj_scr_gen */
/* Purpose : To generate server objects namely logins, devices and databases in a SQL Server */
/* Called From : sp_Script_Generator [Main] */
/* Author : Narasimhan Jayachandran */
/* Date : 09/28/98 */
/* Date : 03/15/00 */
/****************************************************************************************************************/

create proc sp_srv_obj_scr_gen
@.object int,
@.srvObjColl varchar(30),
@.strObjQry varchar(255),
@.strFile varchar(6000),
@.strFilePath varchar(6000)=null
as
begin

declare @.srvObjname varchar(128), @.strMethod varchar(6000),@.hr int,@.return varchar(255), @.strCmd varchar(6000)
declare @.dbCtr int, @.FileCtr int, @.DestFile varchar(6000)

select @.dbctr=0

select @.strCmd = "echo use master >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output
select @.strCmd = "echo go >> " + @.strFile
exec master..xp_cmdshell @.strCmd, no_output

exec("declare server_obj_cursor cursor for "+ @.strObjQry)

open server_obj_cursor

fetch next from server_obj_cursor into @.srvObjname

while @.@.fetch_status = 0
begin
-- select @.srvobjname

select @.strMethod =' '+@.srvObjColl+'("'+@.srvObjname+'").Script(324,"'+@.strFile+'")'

exec @.hr = sp_OAMethod @.object, @.strMethod, @.return out

if @.hr <> 0
begin
close server_obj_cursor
deallocate server_obj_cursor
return 1
end

if @.srvObjColl="Databases" -- database files --
begin
select @.dbCtr = @.dbCtr + 1
select @.strCmd = "copy "+ @.strFile+" "+@.strFilePath+convert(varchar(30),@.dbCtr)+".sql"
end

exec master..xp_cmdshell @.strCmd, no_output

fetch next from server_obj_cursor into @.srvObjname

end

close server_obj_cursor
deallocate server_obj_cursor

if @.srvObjColl="Databases" -- database files --
begin
select @.DestFile=@.strFile
select @.strCmd = "copy ",@.FileCtr=1,@.strFile=" "
while @.FileCtr <= @.dbCtr
begin
select @.strFile = @.strFile + @.strFilePath+convert(varchar(30),@.FileCtr)+".sql + "
select @.FileCtr = @.FileCtr + 1
end
select @.strFile = left(@.strFile,len(@.strFile)-2)

select @.strCmd = @.strCmd+@.strFile+" "+@.destFile
exec master..xp_cmdshell @.strCmd, no_output

select @.strCmd = "del ",@.FileCtr=1
while @.FileCtr <= @.dbCtr
begin
select @.strCmd = "Del "+@.strFilePath+convert(varchar(30),@.FileCtr)+".sql"
exec master..xp_cmdshell @.strCmd, no_output
select @.FileCtr = @.FileCtr + 1
end
end

return 0
end

GO

GRANT EXECUTE ON dbo.sp_srv_obj_scr_gen TO public
GO

--exec sp_script_generator "D:\backup\sqlserver\Script\"

if exists (select * from sysobjects where id = object_id('dbo.sp_script_generator') and sysstat & 0xf = 4)
drop procedure dbo.sp_script_generator
GO
/* ************************************************************************************************************** */
/* Procedure : sp_script_generator */
/* Purpose : To generate scripts for all objects of all databases in a SQL Server 6.5/7.0 */
/* Sub Procedures : sp_srv_obj_scr_gen,sp_db_obj_scr_gen,sp_tb_obj_scr_gen,sp_con_obj_scr_gen */
/* Author : Narasimhan Jayachandran */
/* Version : 1.0 */
/* Date : 09/28/98 */
/* Version : 1.1 */
/* Date : 03/25/99 */
/* Version : 2.0 */
/* Date : 02/15/00 */
/* Usage Hints : sp_Script_Generator <Output File Path>,[SA Password - optional ] */
/* Example : sp_script_generator "c:\scripts\", [SA Password] */
/* Outputs : c:\scripts\SQLSERVER_logins.sql */
/* c:\scripts\SQLSERVER_devices.sql */
/* c:\scripts\SQLSERVER_databases.sql */
/* c:\scripts\SQLSERVER_<databaseName1>.sql */
/* ... */
/* ... */
/* ... */
/* c:\scripts\<SQLSERVER>_<databaseNameN>.sql */
/* Modified Date Reason */
/* 06/25/2001 Jobs Scripts added */
/* 07/03/2001 syslogins used to pick the loginnames */
/* Windows Authentication Mode added */
/* Use the System function ServerName instead of as a parameter */
/****************************************************************************************************************/
create proc sp_script_generator
@.strFilePath varchar(6000) = null,
@.pwd varchar(128) = null

as
begin

declare @.object int, @.hr int,@.return varchar(255)
declare @.dbname varchar(128),@.ObjColl varchar(30)
declare @.strFile varchar(6000), @.strCmd varchar(6000)
declare @.strObjQry varchar(6000), @.strObjQry1 varchar(255), @.strMethod varchar(255)
declare @.dbObjColl varchar(30), @.tbObjColl varchar(30),@.srvObjColl varchar(30)
declare @.dbobjname varchar(30), @.tbobjname varchar(30),@.ObjcollName varchar(60)
declare @.srvObjFlag tinyint,@.sts tinyint,@.TbCtr int,@.ScriptType int
declare @.Ver6570 char(4),@.ProgID varchar(16), @.Property varchar(6000)
declare @.src varchar(255), @.desc varchar(255)

declare @.objJobserver int

set nocount on

if @.strFilePath is null
begin
print "Usage : sp_Script_Generator <Output File Path>, [SA Password]"
print "-"
print "Example : sp_script_generator 'c:\scripts\',[SA Password]"
print "Outputs : c:\scripts\SQLSERVER_logins.sql"
print " c:\scripts\SQLSERVER_devices.sql"
print " c:\scripts\SQLSERVER_databases.sql"
print " c:\scripts\SQLSERVER_<databaseName1>.sql"
print " .."
print " .."
print " .."
print " c:\scripts\SQLSERVER_<databaseNameN>.sql"
print "-"
return
end

-- version check
select @.Ver6570=substring(@.@.version,23,4)

select @.ProgID =
case
when @.Ver6570 = '6.50' then 'SQLOLE.SQLServer'
else 'SQLDMO.SQLServer'
-- when @.Ver6570 = '7.00' then 'SQLDMO.SQLServer'
end

-- Create an object
print "Creating SQL SERVER Object..."
exec @.hr = sp_OACreate @.ProgID, @.object out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
return
end

print "Connecting to the SQL SERVER "+@.Ver6570+"..."
-- Set the property value to true if it is Windows Authentication
if @.pwd is null
begin
-- Set a property
Print "Connecting to SQLServer using Windows Authentication Mode...!"
exec @.hr = sp_OASetProperty @.object, 'LoginSecure', TRUE
if @.hr <> 0
begin
exec sp_OAGetErrorInfo @.object, @.src out, @.desc out
select hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
return
end

-- Connect to the sql server -- Windows Authentication Mode
select @.strMethod = 'Connect("'+@.@.ServerName+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end
else
begin
Print "Connecting to SQLServer using SQLServer Authentication Mode...!"
-- Connect to the sql server
select @.strMethod = 'Connect("'+@.@.ServerName+'" , "sa", "'+@.pwd+'")'
exec @.hr = sp_OAMethod @.object,@.strMethod
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return
end
end

-- Verify that the connection
exec @.hr = sp_OAMethod @.object, 'VerifyConnection' ,@.return out
if @.hr <> 0
begin
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.object
return end

select @.srvObjFlag = 0

-- Server Objects

-- Logins
print "Creating Scripts for "+@.@.servername+" Server Logins..."
select @.strFile=@.strFilePath+@.@.servername+"_logins.sql"
select @.strCmd = "del "+@.strFile
exec master..xp_cmdshell @.strCmd, no_output

select @.srvObjColl = "Logins", @.sts = 0
select @.strObjQry =
case
when @.Ver6570 = "6.50" then
" select name from master..syslogins where status = 8 and name != 'probe' "
else
" select loginname from master..syslogins where loginname not in ('sa') "
end

exec @.sts = sp_srv_obj_scr_gen @.object,@.srvObjColl,@.strObjQry,@.strFile
if @.sts = 1 goto ProcErr

goto CloseDbCur

ProcErr: exec sp_displayoaerrorinfo @.object,@.hr
if @.srvObjFlag = 0 goto ProcFine

CloseDbCur:
close database_cursor
deallocate database_cursor
ProcFine:
exec @.hr = sp_OADestroy @.object
if @.hr <> 0
exec sp_displayoaerrorinfo @.object, @.hr
exec @.hr = sp_OADestroy @.ObjJobServer
if @.hr <> 0
exec sp_displayoaerrorinfo @.ObjJobServer, @.hr

set nocount off
return
end

GO

--

Thanks.

Naras.

|||Actually, you don't need DMO to do this - just search for sp_help_revlogin and install the procedure in your master database. It generates a script which creates all the logins on your server with the correct hash for the password in place for each login.|||

Cool.

Thanks Allen White.

But let him learn DMO...too

Thanks

Naras.