'SQL SERVER' 카테고리의 다른 글
| 현재 Transaction Log를 사용중인쿼리 확인 (0) | 2018.10.26 |
|---|---|
| SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기 (0) | 2018.10.26 |
| user가 가지고 있는 권한 확인 (0) | 2018.10.26 |
| Linked server List (0) | 2018.10.26 |
| 파티션(partition) 테이블 현황조회 (0) | 2018.10.26 |
2018. 10. 26. 21:34
SQL SERVER
-- 작업할 DB를 저장할 임시테이블
SELECT name as [DatabaseName]
INTO #tmpPubDatabases
FROM sys.databases
WHERE database_id > 4
-- object를 저장할 임시테이블
CREATE TABLE #tmpObjects(
DBName VARCHAR(256),
objectName VARCHAR(256),
obj_desc varchar(100),
create_date datetime,
modify_date datetime
);
DECLARE @command VARCHAR(MAX);
DECLARE @condition tinyint;
SET @condition = 7 --일주일 이내에 변경된 데이터 가져올것
-- 변경된 데이터를 불러서 저장
SET @command = '
USE [?]
IF DB_NAME() IN (SELECT DatabaseName FROM #tmpPubDatabases)
BEGIN
INSERT #tmpObjects
SELECT db_name() dbname, name objectName, type_desc as obj_desc, create_date,modify_date
FROM sys.objects
WHERE type IN (''TF'' , ''FN'', ''P'', ''V'', ''PK'', ''F'', ''TR'', ''UQ'', ''C'', ''D'', ''U'')
AND modify_date > getdate() - '+ CAST(@condition AS varchar(2)) +'
END';
-- run for all affected databases
EXEC sp_MSforeachdb @command
-- this will match the publications to the tables and give the you row count and sizes
-- run this in the distribution database
SELECT * FROM
#tmpObjects
--#tempObjects 삭제쿼리 넣기
-- drop table #tmpPubDatabases
-- drop table #tmpObjects
2018. 10. 26. 21:31
SQL SERVER
--user별 권한 확인(특정DB에서만 전체DB검색하는건 아래쪽에 있음)
SELECT
[DatabaseName] = DB_NAME(),
[LoginName] = ulogin.[name],
[DBUserName] = princ.[name],
[UserType] = CASE princ.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[Role] = null,
[PermissionType] = perm. [permission_name],
[PermissionState] = perm. [state_desc],
[ObjectType] = obj. type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME( perm.major_id ),
[ColumnName] = col. [name]
FROM
--database user
sys.database_principals princ
LEFT JOIN
--Login accounts
-- select top 100 * from sys.login_token
sys.syslogins ulogin on princ .[sid] = ulogin .[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm .[grantee_principal_id] = princ .[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col .[object_id] = perm .major_id
AND col .[column_id] = perm .[minor_id]
LEFT JOIN
sys.objects obj ON perm .[major_id] = obj .[object_id]
WHERE
princ.[type] in ('S', 'U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[DatabaseName] = DB_NAME(),
[LoginName] = ulogin.[name],
[DBUserName] = roleprinc.[name],
[UserType] = CASE memberprinc.[type]
WHEN 'S' THEN 'SQL User'
WHEN 'U' THEN 'Windows User'
END,
[Role] = roleprinc. [name],
[PermissionType] = perm. [permission_name],
[PermissionState] = perm. [state_desc],
[ObjectType] = obj. type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME( perm.major_id ),
[ColumnName] = col. [name]
FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc .[principal_id] = members .[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc .[principal_id] = members .[member_principal_id]
LEFT JOIN
--Login accounts
sys.syslogins ulogin on memberprinc .[sid] = ulogin .[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col .[object_id] = perm .major_id
AND col .[column_id] = perm .[minor_id]
LEFT JOIN
sys.objects obj ON perm .[major_id] = obj .[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[DatabaseName] = DB_NAME(),
[LoginName] = '{All Users}',
[DBUserName] = '{All Users}',
[UserType] = '{All Users}',
[Role] = roleprinc. [name],
[PermissionType] = perm. [permission_name],
[PermissionState] = perm. [state_desc],
[ObjectType] = obj. type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME( perm.major_id ),
[ColumnName] = col. [name]
FROM
--Roles
sys.database_principals roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col .[object_id] = perm .major_id
AND col .[column_id] = perm .[minor_id]
JOIN
--All objects
sys.objects obj ON obj .[object_id] = perm .[major_id]
WHERE
--Only roles
roleprinc.[type] = 'R' AND
--Only public role
roleprinc.[name] = 'public' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name] ,
OBJECT_NAME(perm .major_id),
col.[name] ,
perm.[permission_name] ,
perm.[state_desc] ,
obj.type_desc --perm.[class_desc]
--user별 권한 확인(전체DB를 돌면서 진행)
set nocount on
declare @permission table (
Database_Name sysname,
LoginName sysname null,
DBUserName sysname null,
UserType sysname null,
[role] sysname null,
PermissionType sysname null,
PermissionState sysname null,
ObjectType sysname null,
ObjectName sysname null,
ColumnName sysname null
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
SELECT
[DatabaseName] = DB_NAME(),
[LoginName] = ulogin.[name],
[DBUserName] = princ.[name],
[UserType] = CASE princ.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
END,
[Role] = null,
[PermissionType] = perm. [permission_name],
[PermissionState] = perm. [state_desc],
[ObjectType] = obj. type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME( perm.major_id ),
[ColumnName] = col. [name]
FROM
sys.database_principals princ
LEFT JOIN
sys.syslogins ulogin on princ .[sid] = ulogin .[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm .[grantee_principal_id] = princ .[principal_id]
LEFT JOIN
--Table columns
sys.columns col ON col .[object_id] = perm .major_id
AND col .[column_id] = perm .[minor_id]
LEFT JOIN
sys.objects obj ON perm .[major_id] = obj .[object_id]
WHERE
princ.[type] in (''S'', ''U'')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
[DatabaseName] = DB_NAME(),
[LoginName] = ulogin.[name],
[DBUserName] = roleprinc.[name],
[UserType] = CASE memberprinc.[type]
WHEN ''S'' THEN ''SQL User''
WHEN ''U'' THEN ''Windows User''
END,
[Role] = roleprinc. [name],
[PermissionType] = perm. [permission_name],
[PermissionState] = perm. [state_desc],
[ObjectType] = obj. type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME( perm.major_id ),
[ColumnName] = col. [name]
FROM
--Role/member associations
sys.database_role_members members
JOIN
--Roles
sys.database_principals roleprinc ON roleprinc .[principal_id] = members .[role_principal_id]
JOIN
--Role members (database users)
sys.database_principals memberprinc ON memberprinc .[principal_id] = members .[member_principal_id]
LEFT JOIN
--Login accounts
sys.syslogins ulogin on memberprinc .[sid] = ulogin .[sid]
LEFT JOIN
--Permissions
sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col .[object_id] = perm .major_id
AND col .[column_id] = perm .[minor_id]
LEFT JOIN
sys.objects obj ON perm .[major_id] = obj .[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
[DatabaseName] = DB_NAME(),
[LoginName] = ''{All Users}'',
[DBUserName] = ''{All Users}'',
[UserType] = ''{All Users}'',
[Role] = roleprinc. [name],
[PermissionType] = perm. [permission_name],
[PermissionState] = perm. [state_desc],
[ObjectType] = obj. type_desc,--perm.[class_desc],
[ObjectName] = OBJECT_NAME( perm.major_id ),
[ColumnName] = col. [name]
FROM
--Roles
sys.database_principals roleprinc
LEFT JOIN
--Role permissions
sys.database_permissions perm ON perm .[grantee_principal_id] = roleprinc .[principal_id]
LEFT JOIN
--Table columns
sys.columns col on col .[object_id] = perm .major_id
AND col .[column_id] = perm .[minor_id]
JOIN
--All objects
sys.objects obj ON obj .[object_id] = perm .[major_id]
WHERE
--Only roles
roleprinc.[type] = ''R'' AND
--Only public role
roleprinc.[name] = ''public'' AND
--Only objects of ours, not the MS objects
obj.is_ms_shipped = 0
ORDER BY
princ.[Name] ,
OBJECT_NAME(perm .major_id),
col.[name] ,
perm.[permission_name] ,
perm.[state_desc] ,
obj.type_desc --perm.[class_desc]'
)
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
select * from @permission
'SQL SERVER' 카테고리의 다른 글
| SSMS를 이용하여 Excel 데이터 CTRL + V로 DB에 insert 하기 (0) | 2018.10.26 |
|---|---|
| 최근에 수정된 Object찾기 (0) | 2018.10.26 |
| Linked server List (0) | 2018.10.26 |
| 파티션(partition) 테이블 현황조회 (0) | 2018.10.26 |
| SSMS의 Tip (0) | 2018.10.26 |
2018. 10. 26. 21:30
SQL SERVER
Linked Server 조회SELECT ss.server_id ,ss.name ,'Server ' = Case ss.Server_id when 0 then 'Current Server' else 'Remote Server' end ,ss.product ,ss.provider ,ss.catalog ,'Local Login ' = case sl.uses_self_credential when 1 then 'Uses Self Credentials' else ssp.name end ,'Remote Login Name' = sl.remote_name ,'RPC Out Enabled' = case ss.is_rpc_out_enabled when 1 then 'True' else 'False' end ,'Data Access Enabled' = case ss.is_data_access_enabled when 1 then 'True' else 'False' end ,ss.modify_date FROM sys.Servers ss LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
'SQL SERVER' 카테고리의 다른 글
| 최근에 수정된 Object찾기 (0) | 2018.10.26 |
|---|---|
| user가 가지고 있는 권한 확인 (0) | 2018.10.26 |
| 파티션(partition) 테이블 현황조회 (0) | 2018.10.26 |
| SSMS의 Tip (0) | 2018.10.26 |
| Index REBUILD OR REORG 쿼리 (0) | 2018.10.26 |