-- SQL Server Login 계정체크
SELECT Roles.Name
    ,Roles.Type_Desc
    ,Members.Name MemberName
    ,Members.Type_Desc
FROM sys.server_role_members RoleMembers
INNER JOIN sys.server_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.server_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id
WHERE Members.name NOT LIKE '%system%' -- System's
    AND Members.name NOT LIKE '%SQLServer%' -- MSSQL2005 Servieces's
-- DB 사용자체크
SELECT @@Servername AS ServerName
    ,db_name() AS DBName
    ,Roles.Name
    ,Roles.Type_Desc AS RDesc
    ,Members.Name MemberName
    ,Members.Type_Desc AS MDesc
FROM sys.database_role_members RoleMembers
INNER JOIN sys.database_principals Roles ON Roles.Principal_Id = RoleMembers.Role_Principal_Id
INNER JOIN sys.database_principals Members ON Members.Principal_Id = RoleMembers.Member_Principal_Id

 

출처 : http://www.sqler.com/index.php?mid=bColumn&page=63&document_srl=144446

'DataBase > SQL Server' 카테고리의 다른 글

[SQL Server] 데이터 파일, 로그 파일 경로 확인  (0) 2019.01.28
[SQL Server] SSIS Error - Warning: Null value is eliminated by an aggregate or other SET operation.  (0) 2019.01.28
[SQL Server] Service Broker 개요  (0) 2019.01.28
[SQL Server] Error log 확인  (0) 2019.01.28
[SQL Server][MSSQL] Cannot use TEXTIMAGE_ON when a table has no text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml or large CLR type columns.  (0) 2019.01.28

+ Recent posts

티스토리툴바