Baseline script - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Baseline script

Author

Topic

curzonhub
Starting Member

12 Posts

Posted - 2011-11-14 : 10:58:00

- Hi ,I am trying to run this for sql server 2008,Most of it are working but still ,when I run this on sql server 2008 It detects as 2000,and getting some errors..any one can help me?

set nocount on
go

Create Procedure #ObjectsWithGrantOption as
Begin

Create table #Objects ( Username varchar(20), Dbname varchar(20), Objectname Varchar(30), PermissionType varchar(50))

declare @Str varchar(1000)
set @str = ' use

select U.name , ''

'' as dbname,object_name(O.id) as ObjectName ,
case o.Action
when 26 then ''REFERENCES''
when 178 then '' CREATE FUNCTION''
when 193 then '' SELECT''
when 195 then '' INSERT''
when 196 then '' DELETE''
when 197 then '' UPDATE''
when 198 then '' CREATE TABLE''
when 203 then '' CREATE DATABASE''
when 207 then '' CREATE VIEW''
when 222 then '' CREATE PROCEDURE''
when 224 then '' EXECUTE''
when 228 then '' BACKUP DATABASE''
when 233 then '' CREATE DEFAULT''
when 235 then '' BACKUP LOG''
when 236 then '' CREATE RULE''
end as PermissionsType
from sysprotects O
Inner join sysusers U on U.uid = O.UID
where protecttype = 204'

insert into #Objects
exec sp_msforeachdb @str

select * from #Objects
drop table #Objects

End

go

Create Procedure #UsersListWithDBORole as

Begin

declare @str varchar(1000)

Create table #DBRoles(
ID int Identity (1,1),
Db_name varchar(100),
DBRole varchar(100),
UserName Varchar(100))

SET @STR = '
use

select db_name(),DbRole = g.name, UserName = u.name
from sysusers u, sysusers g, sysmembers m,Master..Syslogins l
wHERE l.SID = U.sid
and g.uid = m.groupuid
and u.uid = m.memberuid
and g.name = ''db_owner'' and u.name <> ''dbo'''

insert into #DbRoles
exec master..sp_msforeachdb @str

select convert(char(20),db_name) + ' ' + convert(char(25),userName) + ' ' + convert(char(15),dbrole)
from #DBRoles

drop table #DBRoles

end
go

Create Procedure #GenerateScriptforOrphanUsers
as
Begin

declare @LoginName varchar(200),
@DbName Varchar(100),
@str varchar(1000),
@i int,
@max int,
@J Int,
@MaxJ Int

Create table #OrPhanLogin
(
id int Identity(1,1),
Sid sysname,
Login varchar(100)
)

Create table #DBRoles
(
ID int Identity (1,1),
Db_name varchar(100),
UserName Varchar(100)
)

insert into #OrPhanLogin
exec sp_validatelogins

select @max = max(id) from #OrPhanLogin

set @i = 1

while @i < = @max
begin

Select @LoginName = Login From #OrPhanLogin WHERE ID = @I

SET @STR = '

use

select db_name(),UserName = u.name
from sysusers u,Master..Syslogins l
wHERE l.SID = U.sid
and l.name = ''' + @LoginName + ''''

Truncate table #DbRoles

insert into #DbRoles
exec master..sp_msforeachdb @str

--select * from #DbRoles

select @maxj = max(id) from #DbRoles

set @j = 1

While @j <= @Maxj

Begin
select @DbName=Db_name from #DbRoles where id = @j

print 'use ' + @Dbname
Print 'go'
Print 'exec sp_revokedbAccess ''' + @LoginName + ''''
Print 'go'
set @j = @j + 1

end
set @i = @I + 1
Print 'exec sp_revokelogin ''' + @LoginName + ''''
Print 'go'

End

drop table #DBRoles
drop table #OrPhanLogin

end

go

Create Procedure #ProcePasswordAudit

as

--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)

--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin SYSNAME NULL
,lngPass INTEGER NULL
)

--Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT

--Determine if password is null and user is SQL Login
if (SELECT count(*) FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0 )
> 1
PRINT '###############################################'
PRINT 'The following logins have blank passwords'
PRINT '###############################################'

SELECT name AS 'Login Name' FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0

--Determine if password and name are the same
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)

UPDATE #tLogins
SET lngPass = (SELECT PWDCOMPARE
(@strName,
(SELECT password FROM master.dbo.syslogins WHERE name = @strName)))
WHERE numID = @lngCounter

SET @lngCounter = @lngCounter - 1
END

PRINT '###############################################'
PRINT 'The following logins have passwords the same as their login name'
PRINT '###############################################'
SELECT strLogin AS 'Login Name' FROM #tLogins WHERE lngPass = 1

--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0

--Determine if password is only one character long
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
SET @lngCounter1 = 1
SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
WHILE @lngCounter1 < 256
BEGIN
UPDATE #tLogins
SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),
(SELECT password FROM master.dbo.syslogins WHERE name = @strName)))
WHERE numID = @lngCounter
AND lngPass <> 1

SET @lngCounter1 = @lngCounter1 + 1
END

SET @lngCounter = @lngCounter - 1
END

PRINT '###############################################'
PRINT 'The following logins have one character passwords'
PRINT '###############################################'

SELECT strLogin AS 'Login Name' FROM #tLogins WHERE lngPass = 1

drop table #tLogins
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sql_baselining]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[sql_baselining]
GO

CREATE TABLE [dbo].[sql_baselining] (
[CheckNo] [varchar] (5) ,
[query] [varchar] (8000),
[shortdesc] [varchar] (100) ,
[SeqNo] [int] NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PRC_DBA_BASELINE_SQLSERVER]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PRC_DBA_BASELINE_SQLSERVER]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE PRC_DBA_BASELINE_SQLSERVER
AS
BEGIN

SET NOCOUNT ON

DECLARE @TSQL VARCHAR(8000)

DECLARE CUR_BSLN CURSOR READ_ONLY FOR
SELECT CHAR(10) + 'PRINT ''' + REPLICATE('-', 70) + '''' + CHAR(10)+
'PRINT ''' + CheckNO + '-' + SHORTDESC + '''' + CHAR(10)+
'PRINT ''' + REPLICATE('-', 70) + '''' + CHAR(10)+
QUERY + CHAR(10)+ 'PRINT ''' + REPLICATE('-', 70) + '''' + CHAR(10) AS TSQL
FROM SQL_BASELINING
WHERE QUERY IS NOT NULL
ORDER BY SEQNO

OPEN CUR_BSLN
FETCH NEXT FROM CUR_BSLN INTO @TSQL

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @TSQL IS NOT NULL
EXEC (@TSQL)
-- PRINT @TSQL
FETCH NEXT FROM CUR_BSLN INTO @TSQL
END -- END OF WHILE
CLOSE CUR_BSLN
DEALLOCATE CUR_BSLN
END -- END OF PROC

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

insert into sql_baselining values ('4.0', 'select @@version', 'Version with Patch', 1)
insert into sql_baselining values ('4.1', 'exec sp_msforeachdb ''if exists (select * from ( select name, fileid, filename, filegroup = filegroup_name(groupid), ''''size'''' = convert(nvarchar(15), size * 8) + N'''' KB'''', ''''maxsize'''' = (case maxsize when -1 then N''''Unlimited'''' else convert(nvarchar(15), maxsize * 8) + N'''' KB'''' end), ''''growth'''' = (case status & 0x100000 when 0x100000 then convert(nvarchar(3), growth) + N''''%'''' else convert(nvarchar(15), growth * 8) + N'''' KB'''' end), ''''usage'''' = (case status & 0x40 when 0x40 then ''''log only'''' else ''''data only'''' end) from

.dbo.sysfiles ) a where charindex(''''%'''',growth) > 0 ) print (''''Failed for

'''') else print (''''Success for

'''')''', 'Keep Auto Filegrowth in MB', 1)
insert into sql_baselining values ('4.2', 'if (select sum(size*80/1024/1024) from sysfiles) > 10 begin if(select count(*) from sysfilegroups where groupid !=0) <=1 print ''database size is gt 10gb and the database does not have multiple filegroups'' else print ''Check the Basis for creation of multiple filegroups'' end else print ''Database Size is less than 10GB. No Filegroups Required'' ', 'Database should have more than 1 FG when size > 10 GB', 2)
insert into sql_baselining values ('4.3', 'exec sp_msforeachdb ''if (select databaseproperty(''''

'''',''''isautoupdatestatistics'''') ) = 1 print ''''auto is on for

'''' else print ''''auto is off for

''''''', 'Auto Update statistics should be off', 3)
insert into sql_baselining values ('4.6', 'exec sp_msforeachdb ''if (select count(*) from sysfilegroups) > 1 begin if (select count(*) from sysfilegroups where status = 16 and groupname = ''''PRIMARY'''') = 1 print ''''PRIMARY filegroup should not be default'''' else print ''''

- The Default Filegroup is not PRIMARY'''' end else begin print ''''No Multiple Filegroups'''' end ''', 'Different Default Filegroup', 6)
insert into sql_baselining values ('4.7', 'set nocount on create table #temp(param varchar(50), mn int, mx int, cfg int, rval int) insert into #temp exec sp_configure ''awe enabled'' if ( select rval from #temp ) <> 1 print ''AWE not enabled'' drop table #temp', 'AWE Check', 7)
insert into sql_baselining values ('4.8', 'set nocount on create table #temp3(param varchar(50), mn int, mx int, cfg int, rval int) insert into #temp3 exec sp_configure ''max degree of parallelism'' if ( select rval from #temp3 ) = 0 print ''MaxDOP = 0'' else print ''MaxDOP should be equal to number of physical CPUs'' drop table #temp3', 'Maxdop = number of physical Cpus of the server', 5)
insert into sql_baselining values ('5.2', 'if charindex(''SQL Server 2000'',@@version) > 1 exec sp_msforeachdb ''if (select databasepropertyex(''''

'''', ''''Recovery'''') ) <> ''''FULL'''' print ''''The database

is not in Full Recovery Model'''' '' else Print ''here is SQL 7.0 is running''', 'Recovery Model Check', 8)
insert into sql_baselining values ('7.1', 'create table #temp1(vdesc varchar(8000) ) insert into #temp1 select @@version if(select charindex(''8.00.2039'',vdesc) from #temp1 ) > 0 print ''SQL Server SP4 is installed'' else print ''SP4 is not installed'' drop table #temp1', 'SQL Server Version Check', 9)
insert into sql_baselining values ('7.4A', 'create table #temp2 (val varchar(20), data varchar(30) ) insert into #temp2 exec master..xp_regread N''HKEY_LOCAL_MACHINE'', N''SYSTEM\ControlSet001\Services\MSSQLSERVER'', ''ObjectName'' if (select data from #temp2 ) = ''LocalSystem'' print ''MSSQLServer Service Starts with LocalSystem'' else print ''MSSQLServer Service does not start with LocalSystem'' drop table #temp2', 'MSSQLServer Service Startup Account', 10)
insert into sql_baselining values ('7.4B', 'create table #temp4 (val varchar(20), data varchar(30) ) insert into #temp4 exec master..xp_regread N''HKEY_LOCAL_MACHINE'', N''SYSTEM\ControlSet001\Services\SQLSERVERAGENT'', ''ObjectName'' if (select data from #temp4 ) = ''LocalSystem'' print ''SQLServerAgent Service Starts with LocalSystem'' else print ''SQLServerAgent Service does not start with LocalSystem'' drop table #temp4', 'SQLServerAgent Service Startup Account', 11)
insert into sql_baselining values ('7.6', 'exec sp_msforeachdb ''if exists(select 1 as dbname from

.dbo.sysusers where name = ''''guest'''') print ''''guest user exists in

'''' ''', 'Guest User To be Dropped', 12)
insert into sql_baselining values ('7.9', 'select name + '' XP needs to be disabled'' from master.dbo.sysobjects where name in (''sp_sdidebug'',''xp_cmdshell'',''xp_deletemail'',''xp_readmail'',''xp_runwebtask'',''xp_schedulersignal'',''xp_sendmail'',''xp_snmp_getstate'',''xp_snmp_raisetrap'',''xp_sprintf'',''xp_sscanf'',''xp_startmail'',''xp_stopmail'',''xp_unc_to_drive'',''xp_perfend'',''xp_makewebtask'') and xtype = ''X''', 'Resricted Xps', 13)
insert into sql_baselining values ('7.10', 'if exists(select 1 from master.dbo.sysdatabases where name in (''pubs'', ''northwind'')) print ''Northwind/Pubs database should be dropped.'' else print ''Success 7.10''', 'Northwind and Pubs Database Check', 14)
insert into sql_baselining values ('7.13', 'create table #temp5 (value varchar(10), data int ) insert into #temp5 exec master..xp_regread N''HKEY_LOCAL_MACHINE'',N''SOFTWARE\Microsoft\MSSQLServer\MSSQLServer'',N''AuditLevel'' if (select data from #temp5) <> 3 print ''Logins success/failure auditing is not enabled'' else print ''Enable Auditing of logins success/failure'' drop table #temp5', 'Logins Auditing', 15)
insert into sql_baselining values ('7.14', 'if exists (select * from master.dbo.syslogins where password is null and (isntname = 0 or isntgroup = 0) ) print ''logins with blank password exist'' else print ''No Logins with Blank Passwords'' ', 'Logins with blank Password check', 16)
insert into sql_baselining values ('8.1', 'set nocount on create table #temp6(flag int, status int ) insert into #temp6 exec (''dbcc tracestatus(-1)'') if (select count(*) from #temp6 where flag in (1204,3605,1118)) != 3 print ''Required trace flags are not enabled'' else print ''Required Trace Flags are already present'' drop table #temp6 ', 'Trace Flag Check', 17)
insert into sql_baselining values ('7.16','exec #GenerateScriptforOrphanUsers ','Orphan users',18)
insert into sql_baselining values ('7.17','exec #UsersListWithDBORole ', 'Users with DBO Rights', 19)
insert into sql_baselining values ('7.18', ' Create table #tempObjectNotownedbydbo (dbname varchar(20),objectname varchar(30), Owner varchar(20)) insert into #tempObjectNotownedbydbo exec sp_msforeachdb ''select ''''?'''' as dname, a.name as ObjectName ,b.name as owner From sysobjects A , Sysusers B where a.uid = b.uid and b.name <> ''''dbo'''' and b.name <> ''''INFORMATION_SCHEMA'''' and type in (''''U'''',''''P'''',''''V'''') order by 1'' select * from #tempObjectNotownedbydbo drop table #tempObjectNotownedbydbo', 'Objects Not owned by dbo', 20)
insert into sql_baselining values ('7.19','exec #ObjectsWithGrantOption ' ,'Objects with Grant Option to users', 21)
insert into sql_baselining values ('7.22','exec #ProcePasswordAudit ' ,'Login Audit Result', 22)

go
exec PRC_DBA_BASELINE_SQLSERVER

am

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-14 : 11:02:42

What are the erors? And how does it detect the version?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

curzonhub
Starting Member

12 Posts

Posted - 2011-11-14 : 11:28:25

8.1-Trace Flag Check
----------------------------------------------------------------------
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.

the above is not running..

5.2-Recovery Model Check
----------------------------------------------------------------------
here is SQL 7.0 is running ( I am running on 2008 but result comes as 2000?

am

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-15 : 04:47:35

insert into sql_baselining values ('5.2', 'if charindex(''SQL Server 2000'',@@version) > 1 exec sp_msforeachdb ''if (select databasepropertyex('''''''', ''''Recovery'''') ) <> ''''FULL'''' print ''''The database is not in Full Recovery Model'''' '' else Print ''here is SQL 7.0 is running''', 'Recovery Model Check', 8)

So if it's not 2000 it gives v7 - theres no check for v2008

The other one could come from anywhere - run the statements individually to find out what's causing it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources