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
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.
Cool.
Thanks Allen White.
But let him learn DMO...too
Thanks
Naras.