November 29, 2009 at 8:04 pm
Can any one help me how to write or include the below scripts in to batch files and run on multiple server to get details...
****************script to get builtin/admin acc existed***********
Select @@servername as Servername, getdate() as RunDate
/*
* Show members of serverrole sysadmind
*/
exec sp_helpsrvrolemember @srvrolename = 'sysadmin' -- =SA
go
/*
* show members of the hosting servers builtin\administrators windows group.
* (will only give results if builtin\administrators is activated for SQLServer
*/
EXEC master..xp_logininfo @acctname = 'builtin\administrators',@option = 'members'
go
***************script to get authentication***********************
--SQL 2000/2005 Version
set nocount on
go
DECLARE @SqlPath Nvarchar(255)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
Declare @value_name Nvarchar(20)
Declare @LoginMode_Value int
Declare @LoginMode Nvarchar(15)
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name=@instance_name,
@value=@SqlPath output
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='LoginMode',
@value=@LoginMode_Value output
if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed'
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
END
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'Software\Microsoft\MSSQLServer\MSSQLServer'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name=LoginMode,
@value=@LoginMode_Value output
if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed'
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
END
###################to monitor error log configuration###############
include below sp in to a batch file...
declare @NumErrorLogs int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT
print @NumErrorLogs
November 30, 2009 at 1:48 am
I would suggest you to copy the above code and save it in a sql file on your machine,
let say the file is called as testsql.sql
from command prompt you have to execute the following
sqlcmd -S10.10.10.10 -Usa -Ppass -id:\testsql.sql -od:\results.csv
where
-S server,
-U user,
-P password,
-i is input file,
-o is output file.
The only thing you will not like is the output format of the file, you can try and change the script which will return the output in xml format and then use the above command......
November 30, 2009 at 2:11 am
Also as mentioned above use of sqlcmd, you can run the above test.sql file across multiple servers by using batch mode. List down all servernames in text file and read one by one changing -S parameter for every read.
November 30, 2009 at 2:54 am
hey sejal thanks for extension, can you please show me with example on making this parametrized will be of great help for me as well...
November 30, 2009 at 9:13 am
Hi sejal...
Thanks for the info...
I am not able to figure it out can u plz provide in detail how to run the file on multiple servers
November 30, 2009 at 10:00 am
CANCER (11/29/2009)
Can any one help me how to write or include the below scripts in to batch files and run on multiple server to get details...****************script to get builtin/admin acc existed***********
Select @@servername as Servername, getdate() as RunDate
/*
* Show members of serverrole sysadmind
*/
exec sp_helpsrvrolemember @srvrolename = 'sysadmin' -- =SA
go
/*
* show members of the hosting servers builtin\administrators windows group.
* (will only give results if builtin\administrators is activated for SQLServer
*/
EXEC master..xp_logininfo @acctname = 'builtin\administrators',@option = 'members'
go
***************script to get authentication***********************
--SQL 2000/2005 Version
set nocount on
go
DECLARE @SqlPath Nvarchar(255)
DECLARE @instance_name Nvarchar(30)
DECLARE @reg_key Nvarchar(500)
Declare @value_name Nvarchar(20)
Declare @LoginMode_Value int
Declare @LoginMode Nvarchar(15)
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '9'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\MSSQLServer\MSSQlServer\SuperSocketNetLib\Tcp'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name=@instance_name,
@value=@SqlPath output
if @instance_name is NULL
BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @sqlpath + '\MSSQLServer\'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name='LoginMode',
@value=@LoginMode_Value output
if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed'
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
END
if left(CAST(SERVERPROPERTY('ProductVersion')AS sysname),1) = '8'
BEGIN
select @instance_name = CAST(SERVERPROPERTY('instancename')AS sysname)
if @instance_name is NULL
BEGIN
set @reg_key = 'Software\Microsoft\MSSQLServer\MSSQLServer'
END
ELSE BEGIN
set @reg_key = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instance_name + '\MSSQLServer'
END
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key=@reg_key, @value_name=LoginMode,
@value=@LoginMode_Value output
if @LoginMode_Value = 1
set @LoginMode = 'Windows'
if @LoginMode_Value = 2
set @LoginMode = 'Mixed'
select CAST(SERVERPROPERTY('ServerName')AS sysname) as ServerName, @LoginMode as AuthenticationMode
END
###################to monitor error log configuration###############
include below sp in to a batch file...
declare @NumErrorLogs int
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', @NumErrorLogs OUTPUT
print @NumErrorLogs
How about using powershell !
this may give you a quick start: http://www.mssqltips.com/tip.asp?tip=1684
Hope this helps !
\\K 🙂
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply