![]() Have these audit logs go to one centralized location instead of a local folder.Turn on auditing on your various database and servers you can find more information at.But to manage and view the audits of your entire SQL Server environment, we have created the Centralized Auditing Framework that will parse, load, and report all of your audit logs.įrom a high-level perspective, the architecture to audit sensitive operations for your SQL Server environment will be to: With SQL Server 2008 or higher, there is a powerful yet lightweight method to audit a SQL Server instance. What a great collection of code handy to any DBA level. Thursday, Ma6:06:44 PM - Svetlana Golovko Very helpful code(s) provided for all DBA's I am pleased that other DBAs find it helpful.Įxcellent Post Svetlana. and small request from me, please post Various DMV's and how they are useful in our regular DBA Job Operations. Great Job, Those are really awesome and worthful. ![]() There are great "DMV a Day Series" by Glenn Berry here. Thanks for putting this all together! I will definitely use these. Wednesday, Ap10:39:16 AM - James Lawrence Very useful, and this is exactly what I was looking for. Long story short, Thanks for posting this amazing and useful information. We all are requested to do many things in our day to day jobs but if only we all could collaborate and share information like in this post, we could be so much more better at what we do. Thank you very much Svetlana Golovko, this is really helpfull for new DBA as me, i am having an issue, we are using sql 2008 standard and i can not enable auditing, there i d like to audit the users account in the sysadmin role, some users get removed now and then, i would like to track who is removing the users, can you please assist me, much appreciated. Just add different events when set events with sp_trace_setevent (for example "Audit Add DB User Event"). You can setup server side trace similar to this: Appriciated your hardwork Svetlana.įriday, Janu8:23:54 PM - Svetlana Golovko I always prefer this for my cross reference. Very very nice compilation of great informations and settings You can also use the SSMS built-in report to review a database's backup and restore events: HAVING ISNULL(MAX(b.backup_finish_date), GETDATE()-11) > GETDATE() - 10 SELECT m.name AS DatabaseName, DATABASEPROPERTYEX(m.name, 'Recovery') AS RecoveryMode,ĬASE WHEN ISNULL(MAX(b.backup_finish_date), GETDATE()-10000) 'READ_ONLY' The list returned by this query contains logins that should be reviewed and most likely have to be disabled or deleted: OR (u.stat = 'no_db_permissions' AND p.major_id IS NULL THEN 'no_srv_permissions' ELSE 'na' END srv_permsįROM sys.server_principals l LEFT JOIN sys.server_permissions pĪND ((u.db IS NULL AND p.major_id IS NULL ISNULL(u.stat + ', but is user in ' + u.db +' DB', 'no_db_users') db_perms,ĬASE WHEN p.major_id IS NULL AND r.role_principal_id IS NULL SELECT DISTINCT l.name LoginName, l.type_desc, l.is_disabled, WHERE r.role_principal_id IS NULL AND l.type_desc 'SERVER_ROLE' ON l.principal_id = r.member_principal_id ON l.principal_id = p.grantee_principal_id (SELECT l.name FROM sys.server_principals l LEFT JOIN sys.server_permissions p WHERE u.SID IS NOT NULL AND u.type_desc ''DATABASE_ROLE''' ON u.principal_id = r.member_principal_id ON u.principal_id = p.grantee_principal_id THEN ''no_db_permissions'' ELSE ''db_user'' ENDįROM. SELECT ''?'', CONVERT(varbinary(85), sid) ,ĬASE WHEN r.role_principal_id IS NULL AND p.major_id IS NULL If the value is 1 it is only setup for Windows Authentication.ĬREATE TABLE #all_users (db VARCHAR(70), sid VARBINARY(85), stat VARCHAR(50)) If this returns 0 the server uses both Windows and SQL Server security. ![]() Refer to this tip for more information about guest user account. Any other permissions will be returned by this query as potential problem. Guest user by default has CONNECT permissions to the master, msdb and tempdb databases. SELECT db AS DatabaseName, class_desc, permission_name,ĬASE WHEN class_desc = 'DATABASE' THEN db ELSE ObjectName END as ObjectName,ĬASE WHEN DB_ID(db) IN (1, 2, 4) AND permission_name = 'CONNECT' THEN 'Default'ĮLSE 'Potential Problem!' END as CheckStatus ON p.grantee_principal_id= l.principal_id OBJECT_NAME (major_id, DB_ID(''?'')) as ObjectNameįROM. SELECT ''?'' as DBName, p.class_desc, p.permission_name, Permission_name SYSNAME, ObjectName SYSNAME NULL)
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |