-- 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

 

PS: DB사용자 체크를 잘 돌려서 서버 전체에 이상한 사용자 찾기에도 그만이라는 ^^

이 게시물을...




profile

차주언
MSSQL DBA ,  MCT/ MCDBA
SQL프런티어 /