-LOGINLER CHECKDB UZERINDE INSERT YETKISINE SAHIP OLUYOR. --ONCE CHECKDB OLUSTURULUP GEREKLI TABLOLAR VE TRIGGER CREATE EDILIR. CREATE DATABASE CHECKDB GO USE [CHECKDB] GO /****** Object: Table [dbo].[DBAChangeLog] Script Date: 06/18/2012 12:04:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[DBAChangeLog]( [LogId] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [varchar](256) NULL, [EventType] [varchar](50) NULL, [ObjectName] [varchar](256) NULL, [ObjectType] [varchar](25) NULL, [SqlCommand] [varchar](max) NULL, [EventDate] [datetime] NULL, [LoginName] [varchar](256) NULL, [HostName] [varchar](256) NULL, CONSTRAINT [PK_DBAChangeLog_1] PRIMARY KEY CLUSTERED ( [LogId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[DBAChangeLog] ADD CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()) FOR [EventDate] GO --USERLAR CHECKDB DATABASE ASSIGN EDILIR --LOGINLER BELIRLENIR declare @login varchar(100) DECLARE @CommandToExecute varchar(8000) declare login_cur cursor fast_forward for select l.name from master..syslogins l inner join sys.databases d on l.dbname=d.name where l.name not like '%#%' and l.name not like '%\%' and l.name not in('sa', 'admin') and d.is_read_only=0 and state_desc='ONLINE' order by l.name open login_cur; FETCH NEXT FROM login_cur INTO @login while @@FETCH_STATUS=0 BEGIN SET @CommandToExecute=+char(13)+char(10) +'USE CHECKDB' +char(13)+char(10) +'IF EXISTS(SELECT NAME FROM SYSUSERS )' +char(13)+char(10) + 'BEGIN' +char(13)+char(10) +' DROP USER ['+@login+']' +char(13)+char(10) +'END' +char(13)+char(10) +'CREATE USER ['+@login+'] FOR LOGIN ['+@login+']' +char(13)+char(10) +char(13)+char(10) +'USE CHECKDB' +char(13)+char(10) +'GRANT INSERT TO ['+@login+']' +char(13)+char(10) --print (@CommandToExecute) exec (@CommandToExecute) FETCH NEXT FROM login_cur INTO @login END CLOSE login_cur; DEALLOCATE login_cur; GO /*BURADA DATABASELERDE TABLO VE TRIGGERLARI OLUSTURUR*/ declare @dbname varchar(80) DECLARE @CommandToExecute varchar(8000) --DATABASE ID NUMARASINI KONTROL ET!!BAZEN SYSTEM DATABASE NUMARALI 4-5 DEN KUCUK OLABILIYOR Declare DBName_cur Cursor FAST_FORWARD FOR select name from sys.databases where name not in('tempdb', 'master', 'model', 'msdb', 'ReportServer', 'ReportServerTempDB', 'distribution', 'CHECKDB') and is_read_only=0 order by name Open DBName_cur; FETCH NEXT FROM DBName_cur INTO @dbname WHILE @@FETCH_STATUS = 0 BEGIN set @CommandToExecute='USE '+'['+@dbname+']'+' IF EXISTS (SELECT * FROM sys.triggers WHERE name=''backup_objects'') begin drop trigger backup_objects on database end IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''[DF_EventsLog_EventDate]'') AND type = ''D'') BEGIN ALTER TABLE [DBAChangeLog] DROP CONSTRAINT [DF_EventsLog_EventDate] END /****** Object: Table [dbo].[DBAChangeLog] Script Date: 06/07/2012 17:27:15 ******/ IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N''[DBAChangeLog]'') AND type in (N''U'')) DROP TABLE DBAChangeLog exec('' create trigger backup_objects on database WITH ENCRYPTION for CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION, CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE, CREATE_INDEX, --ALTER_INDEX, DROP_INDEX, CREATE_TABLE, ALTER_TABLE, DROP_TABLE, CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER, --CREATE_STATISTICS, --DROP_STATISTICS, --UPDATE_STATISTICS, CREATE_PARTITION_FUNCTION, ALTER_PARTITION_FUNCTION, DROP_PARTITION_FUNCTION, CREATE_PARTITION_SCHEME, ALTER_PARTITION_SCHEME, DROP_PARTITION_SCHEME, CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA, CREATE_USER, ALTER_USER, DROP_USER, CREATE_VIEW, ALTER_VIEW, DROP_VIEW, CREATE_XML_INDEX as set nocount on declare @data xml declare @Honame varchar(100) set @HoName = Cast(Host_Name() as nvarchar(64)) set @data = EVENTDATA() insert into CHECKDB..DBAChangeLog(DatabaseName, EventType, ObjectName, ObjectType, SqlCommand, LoginName,HostName) values( @data.value(''''(/EVENT_INSTANCE/DatabaseName)[1]'''', ''''varchar(256)''''), @data.value(''''(/EVENT_INSTANCE/EventType)[1]'''', ''''varchar(50)''''), @data.value(''''(/EVENT_INSTANCE/ObjectName)[1]'''', ''''varchar(256)''''), @data.value(''''(/EVENT_INSTANCE/ObjectType)[1]'''', ''''varchar(25)''''), @data.value(''''(/EVENT_INSTANCE/TSQLCommand)[1]'''', ''''varchar(max)''''), @data.value(''''(/EVENT_INSTANCE/LoginName)[1]'''', ''''varchar(256)''''), @Honame ) ''); ' --exec (@CommandToExecute) print(@CommandToExecute) FETCH NEXT FROM DBName_cur INTO @dbname END CLOSE DBName_cur; DEALLOCATE DBName_cur;
