paraDisE beyond the eArTH

Showing posts with label IT» SQL. Show all posts

Monday 3 October 2011

Auditing SQL 2008

Auditing SQL Server Password Age

-- Show all logins where the password is over 60 days old
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE());

-- Show all logins where the password is over 60 days old disregarding specific SQL Server Logins [##MS_PolicyTsqlExecutionLogin##, ##MS_PolicyEventProcessingLogin##]
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged'
FROM sys.sql_logins
WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') < DATEADD(dd, -60, GETDATE())
AND NOT (LEFT([name], 2) = '##' AND RIGHT([name], 2) = '##');

-- Show all logins where the password was changed within the last day
SELECT name, LOGINPROPERTY([name], 'PasswordLastSetTime') AS 'PasswordChanged' FROM sys.sql_logins WHERE LOGINPROPERTY([name], 'PasswordLastSetTime') > DATEADD(dd, -1, GETDATE());
O
R
-- Show all logins where the password is more than 1 month

Select loginname from master..syslogins where datediff(Month,updatedate,getdate()) > 1

reference site: http://www.mssqltips.com/sql_server_dba_tips.asp

Tips to optimize SQL server performance

Top 10 Tips for Optimixing SQL Server

10. Facilitate comparisons of workload behavior with benchmarking. What are baselining and benchmarking?

9. Use performance counters to quickly get useful information about currently running operations. Operational Monitoring & Bottleneck Monitoring.
8. Understand why changing server settings usually yields limited returns.
7. Identify performance bottlenecks quickly with DMVs.
6. Learn to use SQL Profiler and traces.
5. See why SANs are more than just I/O.
4. Prevent cursors and other bad T-SQL from returning to haunt applications.
3. Maximize plan reuse for better SQL Server caching.
2. Read the SQL Server buffer cache and how to minimize cache thrashing.
1. Master indexing by learning how indexes are used and how to counteract the characteristics of bad indexes.

read white paper:

http://www.quest.com/Quest_Site_Assets/WhitePapers/Top_10_Tips_for_Optimizing-final.pdf