[no subject]

Our Microsoft SQL admin has this "quarterly DB audit script" which he
runs on our Microsoft SQL boxes

And uses internal database names from Microsoft sql to get information .



I am looking for something comparable to appease our auditors for the
iSeries

I've been given the name of a product called BrotherWatchdog

But at $10k it's a bit pricey.



I've looked around on some iSeries sites for some example of something
close

Using DB2 nomenclature....and internal system table to query off of.



But either I am looking in the wrong spot or it something I just don't
know where to look



Is creating a script like this which get info from system internal
tables

Even realistic w/o getting a specialized utility/product.







/** script1 **/



SET NOCOUNT ON

GO

USE master

GO



PRINT 'Server Audit for:'

SELECT CONVERT(char(20), SERVERPROPERTY('servername'))

GO

PRINT ''





/* Check to make sure they're up to the latest version.

*/

PRINT 'NNN Minimum Approved Version of MS SQL Server is 8.0.2039'

PRINT 'If this is not the version shown below'

PRINT 'this system is below minimum approved requirements.'

PRINT 'Current MS SQL Server Version:'



SELECT @@VERSION



/* Get a list of all the databases on the system.

*/



PRINT 'The following databases exist on this system:'

PRINT ''



SELECT SUBSTRING(name,1,30) AS database_name

FROM sysdatabases



/* Check to see if the "northwind" or "pubs" database exist.

* if they do, warn the user to be proactive and remove

* the default databases.

*/



IF(EXISTS (SELECT dbid FROM sysdatabases WHERE name = 'pubs'))

PRINT 'It looks like you have not removed the default databases *pubs*
from

your system. You may want to do that.'

PRINT ''



IF (EXISTS (SELECT dbid FROM sysdatabases WHERE name = 'Northwind'))

PRINT 'It looks like you have not removed the default database
*Northwind* from

your system. You may want to do that.'

PRINT ''



/* Check for logins with null passwords or

* existence of a 'guest' account

* (which would be bad).

* note that NT logins will have NULL passwords, since

* NT passwords are not stored in the database.

*/



PRINT 'The following logins have null passwords'

PRINT 'and ARE NOT NT logins:'

PRINT ''

SELECT (SUBSTRING(loginname,1,32))AS loginname

FROM syslogins

WHERE password is NULL

AND isntgroup = 0

AND isntuser = 0

OR loginname = 'guest'



/** script3a **/

print
'=======================================================================
========'

print 'sp_server_info'

go

sp_server_info

go

print
'=======================================================================
========'

print 'sp_configure'

go

sp_configure

go

print
'=======================================================================
========'

print 'xp_loginconfig'

go

xp_loginconfig

go

print
'=======================================================================
========'

print 'xp_msver'

go

xp_msver

go

print
'=======================================================================
========'

print 'sp_helpdb'

go

sp_helpdb

go

print
'=======================================================================
========'

print 'sp_helpsrvrole'

go

sp_helpsrvrole

go

print
'=======================================================================
========'

print 'sp_helpsrvrolemember'

go

sp_helpsrvrolemember

go

print
'=======================================================================
========'

print 'sp_helpdbfixedrole'

go

sp_helpdbfixedrole

go

print
'=======================================================================
========'

print 'sp_helplogins'

go

sp_helplogins

go

print
'=======================================================================
========'

print 'select * from sys.server_principals'

go

select * from sys.server_principals

go

print
'=======================================================================
========'

print 'select * from sys.sql_logins'

go

select * from sys.sql_logins

go

print
'=======================================================================
========'

print 'select * from sysusers'

go

select * from sysusers

go

print
'=======================================================================
========'

print 'select * from sysservers'

go

select * from sysservers

go

print
'=======================================================================
========'

print 'sp_helpserver'

go

sp_helpserver

go

print
'=======================================================================
========'

print 'sp_helpremotelogin'

go

sp_helpremotelogin

go

print
'=======================================================================
========'

print 'sp_linkedservers'

go

sp_linkedservers

go

print
'=======================================================================
========'

print 'sp_helplinkedsrvlogin'

go

sp_helplinkedsrvlogin

go

print
'=======================================================================
========'

print 'sp_helpextendedproc'

go

sp_helpextendedproc

go

print
'=======================================================================
========'

go





/** script3b **/

print
'=======================================================================
========'

print 'sp_helprole'

go

sp_helprole

go

print
'=======================================================================
========'

print 'helprolemember'

go

sp_helprolemember

go

print
'=======================================================================
========'

print 'helpprotect'

go

sp_helprotect

go

print
'=======================================================================
========'

print 'sp_helpuser'

go

sp_helpuser

go

print
'=======================================================================
========'

print 'sp_helpfile'

go

sp_helpfile

go

print
'=======================================================================
========'

print 'sp_stored_procedures'

go

sp_stored_procedures

go



print
'=======================================================================
========'

SET NOCOUNT ON

GO



DECLARE @database VARCHAR(50)



SET @database = DB_NAME()





/* Get a list of all the users in this database

* from the sysusers system table.

*/



PRINT 'The following users exist in this database:'

PRINT ''



SELECT SUBSTRING(name,1,30) AS name

FROM sysusers

WHERE issqluser =1

OR isntuser = 1

OR isntgroup = 1

print
'=======================================================================
========'



/* Get all the roles in the current database.

*/

PRINT 'The following user roles exist in this database:'

PRINT ''



SELECT SUBSTRING(name,1,30) AS role_name

FROM sysusers

WHERE issqlrole = 1

print
'=======================================================================
========'



/* Figure out who has access to stored procedures. According

* to Microsoft, all the permission information is actually

* stored in the syspermissions table, but it's not very-well

* documented, so I'm using the sysprotects view.

*/



PRINT 'The following stored procedures are executable (action = 224)'

PRINT 'by the people listed in user_name:'

PRINT ''



SELECT SUBSTRING(sysobjects.name,1,30) AS procedure_name,

SUBSTRING(sysusers.name,1,30) AS user_name

FROM sysobjects, sysprotects, sysusers

WHERE sysobjects.id = sysprotects.id

AND sysusers.uid = sysprotects.uid

AND sysobjects.xtype = 'P'

AND sysprotects.action = 224

print
'=======================================================================
========'



USE msdb

GO



print
'=======================================================================
========'



/* List active jobs, steps and schedules

*/



PRINT 'The following jobs/steps/schedules are active'

PRINT ''



SELECT B.Name as JobName, A.step_name as StepName, A.step_id,
A.subsystem, A.database_name,

D.freq_type, D.freq_interval, D.freq_subday_type,
D.freq_subday_interval, D.freq_relative_interval,
D.freq_recurrence_factor, D.active_start_time

from sysjobsteps A

JOIN sysjobs B ON (A.job_id = B.job_id)

JOIN sysjobschedules C ON (C.job_id = B.job_id)

JOIN sysschedules D ON (D.schedule_id = C.schedule_id)

WHERE B.enabled = 1

ORDER BY B.Name, A.step_id



print
'=======================================================================
========



------------------------------------------------------------------------
---



Jim Norbut

Systems Administrator