May 30, 2008 at 7:48 am
First, too many users in SA role is trouble, Also check the Applicable permissions for BUILTIN/Administrators (any admin users on the box have default access to SQLServer as Admin).
I always use DDL Events to Monitor and keep everything under check on my SQL 2005 boxes.
Check Event Viewer in Windows and also Error Log in SQL Server.
Also if Possible try Running the Default Reports for SQL 2005. right Click SQL Server name and goto Reports-> browse around a coupel of Reports to get an exact idea of their structure... Check configuration Changes and Object history change..
Better yet, Create your own. Create a Database for Auditing SERVER.
Keep it private. Runall kinds of Queries and Store the Data into this DB.
I use it for the Following:
Check Service Status across all SQL Servers. (Ping every 30 mins)
Check Error Log status (Read error Log every few hours and Store it here)
Check Database Growth across all servers and DB's
Audit all logins across all servers (even Databases)
Select * from master.dbo.syslogins where createdate>= getdate()-1 or .... >=dateadd(hour,-3,getdatE())
Check sysprocesses and Store the info in the table (to compare)
other things.
Last Words: Live the DBA world, dont just LABEL it. Sounds Easy but is HARD.
This shouldnt have happened my Friend... it can cost you your Job...
Maninder
www.dbanation.com
May 30, 2008 at 7:59 am
Mani raises very good points.
Auditing is a must in a prod environment, as is removing BUILTIN\ADMINS from a prod instance.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 30, 2008 at 8:12 am
Auditing is the organization policy. Every organization has policy to audit the servers in a year or so. It's a DBA responsibility that he/she has to make sure to make his/her DB/Server as per the MS Best practice.
1) Always SQL should be running on svc a/c.
2) Remove BuiltinAdmin login from the server.
3) Try to avoid the SQL a/c(SA)
4) Every user can access the server with windows authentication only.
5) Do not give SA permission to any user, if giving ask for exception.(Why need, what he/she will do)
6) Use monitoring tool from3rd party tool or use SQL script for tracking your server/DB.
7) Try to Audit your server twice in a year or to be safe side Quarterly basis.
8) Involve your manager at the time of Audit.
9) This should be your Internal Audit.(Make your System as neet and clean as possible, you should not be able to expalin to External aAuditors in brief).
MCP, MCTS (GDBA/EDA)
March 30, 2009 at 6:13 am
Thanks all 😀
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply