Checks Common Database Problems
Lists users in group public if groups are used.
Warn about lack of groups if no groups exist besides public.
List users aliased to another non dbo user.
List aliases without logins (login previously dropped).
List users without logins (login previously dropped).
List objects owned by non-dbo (maybe poor code control?).
Find objects with access to syslogins in them. This
procedure excludes normal objects like sp__addlogin. Use
of this procedure will identify potential Trojan horses.
Find any objects with public access.
If not master db, list any objects starting with "sp_" that
are also in master (Trojan horses).
Database has not had transaction log dump in 24 hours.
Checks Object / Comment mismatch (hand deleted, or rename)
Create object permissions granted to users
USAGE
sp_auditdb [@srvname, @hostname ]
External programs that collect errors can pass the parameters parameters (@srvname & @hostname) to the procedure. If these are passed, the procedure will print a slightly different return set that includes error numbers and other information.
SEE ALSO
sp_auditsecurity
ACCESS
This procedure can be only runable by sa because it may reveal information that can help an intruder..
SAMPLE OUTPUT
1> sp_audidb
Error
---------------------------------------------
Object get_comn_syslogins has access to syslogins
Object get_comn_sysusers has access to syslogins
Object get_comn_sysusers has access to syslogins
User sa is a member of group public
Group Public access to object pb_catcol type=P
Group Public access to object pb_catedt type=P
Group Public access to object pbcatfmt type=U
Group Public access to object pbcattbl type=U
DESCRIPTION
Reports Users With Passwords like the Username
Reports Users With Null Passwords
Reports Users With Short (<=4 character) Passwords
Reports Users With Master/Model/Tempdb Database As Default (except sa)
Reports allow updates is set
Reports Users with stupid passwords like "sybase"....
USAGE
sp_auditsecurity [@print_only_errors,] [@srvname, @hostname ]
if @print_only_errors is not null then prints only errors. Otherwise it will print statements about successes
Programs that collect errors can pass the parameters parameters (@srvname & @hostname) to the procedure. If these are passed, the procedure will print a slightly different return set that includes error numbers and other information.
SEE ALSO
sp_auditdb
ACCESS
This procedure is only runable by sa because it reveals users with weak passwords.
SAMPLE OUTPUT
1> sp_auditsecurity
Security Violations
------------------------------------------------------
(No Users With Null Passwords)
User monitor Has master Database As Default
User mon6 Has master Database As Default
User a Has master Database As Default
Allow Updates is Set
(Allow Updates is Not Set)
(No Trusted Remote Logins)
DESCRIPTION
Identifies bad indexes according to the following rules. Finds indexes containing null, vbl lth, text, or image columns. Find indexes over 30 bytes long or indexes that have never had statistics updated on them. List NC indexes on small tables.
USAGE
sp_badindex [ @tablename ]
SAMPLE OUTPUT
1> exec sp_badindex
Table/Index Name Description Problem Found
------------------------------ ---------------------- --------------
alerts.XPKalerts Length = 60 >30 Byte Index
alerts.XPKalerts srvname char(30) Allows Null
audit_trail.XPKaudit_trail Length = 38 >30 Byte Index
comn_database.XPKdatabase Length = 60 >30 Byte Index
comn_dumpdevices.XPKcomn_dumpd Length = 60 >30 Byte Index
comn_syscolumns.XPKcomn_syscol Length = 94 >30 Byte Index
Creates bcp in / out shell script that can be used to extract info from the database.
sp_bcp {server}, [database], [user], [password], [direction], [extension], [commands]
where...
{@server} Server name (should really be entered since
@@servername is rarely defined)
[@database] Defaults to database procedure is run in
[@user] Defaults to current username
[@password] Defaults to current password
[@direction] "out" or "in". Defaults to out
[@extension] File extension will default to .dat
[@commands] Allows you to enter further switching commands
(-c option to bcp)
BUGS
It is advisable to run the stored procedure through isql in at least 132 column mode (-w132) to stop the crummy (isql) program from inserting linefeeds.
SAMPLE OUTPUT
1> use master
1> sp_bcp SYBASE,master,sa,xxx
echo ""
echo ""
echo BCP out table master..spt_committab
bcp master..spt_committab out spt_committab.dat -Usa -Pxxx
-SSYBASE -c
echo ""
echo ""
echo BCP out table master..spt_values
bcp master..spt_values out spt_values.dat -Usa -Pxxx -SSYBASE
DESCRIPTION
Monitor Blocked Processes
SEE ALSO
sp_block sp_lockt
SAMPLE OUTPUT
1> sp_block
SPID User Host Program Blocking on Table Lock Type
---- ---------- ------ ---------- -------------------- ---------------
7 giraffe AARDVARKS_R_US Update_page-blk
7 giraffe AARDVARKS_R_US Ex_page-blk
Blocked SPID Blocked User Host Program Blocked By SPID
------------ ------------ ---------- ---------- ---------------
26 edisking 7
26 ediswise 7
45 iamrich 7
45 iampoor 7
DESCRIPTION
Create script to check foreign key relationships. For example if you have a field stor_id in table sales that indicates the store the sales are for, the script output lists stor_id's in sales that do not have rows in store. The script uses foreign keys that you have set up.
SAMPLE OUTPUT
1> sp_checkkey
[ for each foreign key in database ]
declare @cnt int
set nocount on
select title_id into #tmp from roysched
delete #tmp from #tmp p,titles d where p.title_id=d.title_id
if exists ( select * from #tmp )
begin
select @cnt=count(*) from #tmp
if @cnt>=100
print 'first 100 keys in roysched w/o data in titles'
else
print 'distinct keys in roysched w/o data in titles'
set rowcount 100
select distinct * from #tmp
set rowcount 0
end
drop table #tmp
go
[ WHEN RUN THIS SCRIPT PRODUCES ]
1> [ execute above ]
distinct keys in roysched w/o data in titles
title_id
----------
A12224
DESCRIPTION
Reports column with multiple definitions (for example, one. defined in table A as an int and in B as a smallint).
USAGE
sp_colconflict [ @objectname ]
@objname specifies objects to select (query like "%@objname%")
SEE ALSO
sp_helpcolumn,sp_collist
sp_helpnulls
SAMPLE OUTPUT
1> exec sp_colconflict
Column Table Defn Null
-------------------- -------------------- --------------- --------
attribute schedule_history char(30) null
attribute schedule_attributes varchar(127) null
description error_severity char(18) null
description system char(255) null
description disks char(30) null
description user_view char(30) null
description remarks varchar(127) null
description hardware varchar(127) null
group_name schedule_defn char(18) not null
group_name schedule_groups char(18) not null
group_name comn_sysusers char(30) not null
group_name model char(30) null