1. Procedure / Non-Table Function Grant 하기
DECLARE @ACCOUNT VARCHAR(100)
SET @ACCOUNT = 'gfsadm, gfswas, gfsifadm, gfsifwas, gfsportaladm, gfsportalwas'
SELECT 'GRANT EXECUTE ON ' + name + ' TO ' + @ACCOUNT FROM sys.procedures SELECT 'GRANT EXECUTE ON ' + SPECIFIC_NAME + ' TO ' + @ACCOUNT FROM information_schema.routines WHERE routine_type = 'function' AND data_type !='TABLE';
-- 수행결과 Script 로 권한 부여
-- 참고 1) 테이블에 CRUD 권한이 없어도, PROCEDURE EXECUTE 권한으로 TABLE DML 이 가능함
2. 미사용유저 삭제 시 안될 경우
select * from sys.schemas where principal_id = user_id ('test1')
alter authorization on schema::db_backupoperator to dbo ;
3. -- Invalid DB Object
SELECT s.type_desc, OBJECT_NAME(referencing_id) AS [this sproc or VIEW...], referenced_entity_name AS [... depends ON this missing entity name] FROM sys.sql_expression_dependencies as d JOIN sys.objects as s on d.referencing_id = s.object_id WHERE is_ambiguous = 0 AND OBJECT_ID(referenced_entity_name) IS NULL ORDER BY OBJECT_NAME(referencing_id), referenced_entity_name;
4. -- Drop INvalid Object
SELECT distinct case s.type when 'P' then 'drop procedure ' + OBJECT_NAME(referencing_id) when 'V' then 'drop view ' + OBJECT_NAME(referencing_id) when 'FN' then 'drop function ' + OBJECT_NAME(referencing_id) end as query FROM sys.sql_expression_dependencies as d JOIN sys.objects as s on d.referencing_id = s.object_id WHERE is_ambiguous = 0 AND OBJECT_ID(referenced_entity_name) IS NULL;
5. filegroup 나눌 경우, nonclustered index 재생성 스크립트 작성하기
DECLARE @filegroup nvarchar(20); -- set the filegroup name where the index will be located after the re-creation SET @filegroup = 'GRP_INDEX'; SELECT ' CREATE ' + CASE WHEN I.is_unique = 1 THEN ' UNIQUE ' ELSE '' END + I.type_desc COLLATE DATABASE_DEFAULT +' INDEX [' + I.name + '] ON [' + Schema_name(T.Schema_id)+'].['+T.name + '] ( ' + KeyColumns + ' ) ' + ISNULL(' INCLUDE ('+IncludedColumns+' ) ','') + ISNULL(' WHERE '+I.Filter_definition,'') + ' WITH ( ' + CASE WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON ' ELSE ' PAD_INDEX = OFF ' END + ',' + 'FILLFACTOR = '+CONVERT(CHAR(5),CASE WHEN I.Fill_factor = 0 THEN 100 ELSE I.Fill_factor END) + ',' + -- default value 'SORT_IN_TEMPDB = OFF ' + ',' + CASE WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON ' ELSE ' IGNORE_DUP_KEY = OFF ' END + ',' + CASE WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF ' ELSE ' STATISTICS_NORECOMPUTE = ON ' END + ',' + -- default value ' DROP_EXISTING = ON ' + ',' + -- default value ' ONLINE = ON ' + ',' + CASE WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON ' ELSE ' ALLOW_ROW_LOCKS = OFF ' END + ',' + CASE WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON ' ELSE ' ALLOW_PAGE_LOCKS = OFF ' END + ' ) ON ['+@filegroup+'] ' [CreateIndexScript] FROM sys.indexes I JOIN sys.tables T ON T.Object_id = I.Object_id JOIN sys.sysindexes SI ON I.Object_id = SI.id AND I.index_id = SI.indid JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , [' + C.name + ']' + CASE WHEN MAX(CONVERT(INT,IC1.is_descending_key)) = 1 THEN ' DESC ' ELSE ' ASC ' END FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 0 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id ORDER BY MAX(IC1.key_ordinal) FOR XML PATH('')), 1, 2, '') KeyColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id ,IC2.index_id) tmp3 )tmp4 ON I.object_id = tmp4.object_id AND I.Index_id = tmp4.index_id JOIN sys.stats ST ON ST.object_id = I.object_id AND ST.stats_id = I.index_id JOIN sys.data_spaces DS ON I.data_space_id=DS.data_space_id JOIN sys.filegroups FG ON I.data_space_id=FG.data_space_id LEFT JOIN (SELECT * FROM ( SELECT IC2.object_id , IC2.index_id , STUFF((SELECT ' , [' + C.name + ']' FROM sys.index_columns IC1 JOIN Sys.columns C ON C.object_id = IC1.object_id AND C.column_id = IC1.column_id AND IC1.is_included_column = 1 WHERE IC1.object_id = IC2.object_id AND IC1.index_id = IC2.index_id GROUP BY IC1.object_id,C.name,index_id FOR XML PATH('')), 1, 2, '') IncludedColumns FROM sys.index_columns IC2 GROUP BY IC2.object_id ,IC2.index_id) tmp1 WHERE IncludedColumns IS NOT NULL ) tmp2 ON tmp2.object_id = I.object_id AND tmp2.index_id = I.index_id WHERE I.is_primary_key = 0 AND I.is_unique_constraint = 0 and i.type_desc = 'NONCLUSTERED' and ds.name <> @filegroup