SQL Server / 모든 사용자에 대한 모든 권한을 com/go/downloads_kr 쿼리하지 데이터베이스에 액세스

Question

원본 비 AMP 버전 편집

SQL Server / 모든 사용자에 대한 모든 권한을 com/go/downloads_kr 쿼리하지 데이터베이스에 액세스

Sql server 2008년 에서 쿼리하지 쓸 수 있는 모두 보고 싶다 액세스하도록 사용자 또는 객체가 등 특정 데이터베이스, 테이블, 뷰, 저장 프로시저 직접, 또는 데이터베이스 내의 인해 역할 등에. 이 보고서는 약간만이라도 사용되는 보안 감사를. 만약 누군가 나의 요구에 맞게 될 수 있는지 알고 있다, 그러나 나를 완전히 쿼리하지 생각하신거야 부여하느뇨 좋은 출발을 기대해 본다. Sql 2008년, 2005-2000년 할 수 있는데, 필요에 따라 변환하시겠습니까 인컨텍스트 것이다.

Jeremy

편집된 질문14일 8월 2011 в 8:40

프로그래밍

sql-server-2005 sql-server sql-server-2008

13일 8월 2011 в 5:46

4 파운드

질문에 대한 의견 (2)

Sean Rose

원본 비 AMP 버전 편집

다음은 8월 2011년 제안한 쿼리하지 변경 사항을 포함한 완전한 버전의 Jeremy& # 39 의 브래드 (10월 2011년) 과 세바스쿠친 (5월 2012년) 내장했습니다:

Brad:* 올바른 ' [ObjectType]' 와 ' [개체 이름] 1.' 는 스키마에는.
iw.kuchin:* [ObjectType] 's better it& # 39 의 2.', '' 허가 '클래스' OBJECT_OR_COLUMN obj.type_desc 대해서만 사용할 수 있습니다. 다른 모든 사용할 경우 '페름. [class_desc]'.
iw.kuchin:* 처리하십니까 '가장' 권한만으로. 3.
iw.kuchin:* sys.login_token sys.server_principals 굈 '표시' 대로 4. '을 (를)' 또한 sql 로그인을 indows 아니라 수준들과.
iw.kuchin:* indows 류되나니 포함 5.
사용자 sys '와' iw.kuchin:* 제외할지 INFORMATION_SCHEMA. 6.

이를 통해 다른 사람을 위해 목숨을 걸고 한 시간 또는 두 개의 기대해 본다. '':)

/*
Security Audit Report
1) List all access provisioned to a SQL user or Windows user/group directly
2) List all access provisioned to a SQL user or Windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserType        : Value will be either 'SQL User', 'Windows User', or 'Windows Group'.
                  This reflects the type of user/group defined for the SQL Server account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
LoginName       : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE,
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
Schema          : Name of the schema the object is in.
ObjectName      : Name of the object that the user/role is assigned permissions on.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.
*/

    --1) List all access provisioned to a SQL user or Windows user/group directly
    SELECT
        [UserType] = CASE princ.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = princ.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = NULL,
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Database user
        sys.database_principals            AS princ
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

73

0

해설 (3)

Andomar

원본 비 AMP 버전 편집

이를 위해 시스템 뷰는 에서 SQL Server 2005년 에서 사용할 수 있습니다. 예를 들어, 이 질의에서는 모든 사용자가 데이터베이스의 열거합니다 그들의 권리:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id

주의해야 할 수 있는 권한을 가지고 있는 사용자 역할을 통해 잘 알려져 있다. 예를 들어, '' 선택 '역할에 대한 권한을 db_data_reader 허가합니다 객체에는 대부분.

50

0

해설 (2)

iw.kuchin

원본 비 AMP 버전 편집

39, t 에 대한 의견 수락됨 오토메이티드 그러하매 I& can& # 39, ll 추가 일부 설명을 있습니다.

I 의 두 번째 브래드 스키마에는 배정됩니다. Ms 에서 '만' 시스트로비치스 참조 테이블에 스키마에는 범위지정 객체에는. 그래서, 높은 level&quot &quot 대한 정보를 얻을 수 있습니다. 객체 (예: 스키마에는 emc 의 경우) 시s.셰마스 '표' 를 사용해야 합니다.
[ObjectType] ',' s better it& '대한' 허가 'OBJECT_OR_COLUMN # 39 obj.type_desc 대해서만 사용할 수 있는' 클래스. 다른 모든 경우에 대해 [class_desc] '사용' 페름.
다른 유형의 권한이 없는 처리됩니까 잘 대체하십시오 질의입니다 '가장'. 왼쪽 참가하십시오 sys.database_principals '에 대한 정보를 얻기 위해 반드시 1 명' 을 (를) '' perm.major_id imp.principal_id 마쳤다 '='
39 의 it& 굈 '내 경험 있는 것이 아니라' 표시 '을 (를)' sys.server_principals sys.login_token 않을 뿐만 아니라 또한 sql 로그인을 indows 수준들과
39, & # 39 G& 반드시 1 명 추가 ',' 를 사용할 수 있도록 indows 그룹 주요 유형
또한 사용자 sys '와' 표 '에서 INFORMATION_SCHEMA 가르칠 수 있다는 한 결과 같은 서비스 사용자 대해서만 사용할 수 있습니다

39, ll 게시물로의 i& 첫 부분을 수정, 변경해야 할 뿐만 아니라 다른 모든 부품이 함께 스크립트입니다 제안했다.

SELECT  
    [UserName] = ulogin.[name],
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                    WHEN 'G' THEN 'Windows Group'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = CASE perm.[class] 
                        WHEN 1 THEN obj.type_desc               -- Schema-contained objects
                        ELSE perm.[class_desc]                  -- Higher-level objects
                   END,       
    [ObjectName] = CASE perm.[class] 
                        WHEN 1 THEN OBJECT_NAME(perm.major_id)  -- General objects
                        WHEN 3 THEN schem.[name]                -- Schemas
                        WHEN 4 THEN imp.[name]                  -- Impersonations
                   END,
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.server_principals 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]
LEFT JOIN
    sys.schemas schem ON schem.[schema_id] = perm.[major_id]
LEFT JOIN
    sys.database_principals imp ON imp.[principal_id] = perm.[major_id]
WHERE 
    princ.[type] IN ('S','U','G') AND
    -- No need for these system accounts
    princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

24

0

해설 (4)

Chef Slagle

원본 비 AMP 버전 편집

뛰어난 스크립트입니다 제레미랑 투고자! 감사합니다!

I have a 는 사용자 s 급 실행하기 때문에 모든 사용자에 대한 악몽을 되풀이했다. 그래서 저는 제가 couldn& t # 39, 주석 추가, 변경 사항을 포함한 전체 스크립트입니다 보였다. 그래서 내가 추가한 가변으로 + where 절 검색할 수 있는 최대 5 자 일치하는 것도 사용자 이름 (또는 모든 사용자가 비어 있음). 하지만 데 도움이 될 것 같아 활용 사례를 몇 가지 특별한 게 없다.

DECLARE @p_userName NVARCHAR(5) = 'UName' -- Specify up to five characters here (or none for all users)

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user cccount.  This could also be an            Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT, DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on. This value may not be populated for all roles.  Some built in roles have implicit permission definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value is only populated if the object is a table, view or a table value function. 

*/

DECLARE @userName NVARCHAR(4) = @p_UserName + '%'
--List all access provisioned to a sql user or windows user/group directly 

SELECT  
[UserName] = CASE princ.[type] 
                WHEN 'S' THEN princ.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE princ.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END,  
[DatabaseUserName] = princ.[name],       
[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
sys.login_token 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')  
AND princ.[name] LIKE @userName  -- Added this line --CSLAGLE
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
[UserName] = CASE memberprinc.[type] 
                WHEN 'S' THEN memberprinc.[name]
                WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
             END,
[UserType] = CASE memberprinc.[type]
                WHEN 'S' THEN 'SQL User'
                WHEN 'U' THEN 'Windows User'
             END, 
[DatabaseUserName] = memberprinc.[name],   
[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.login_token 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]
WHERE memberprinc.[name] LIKE @userName -- Added this line --CSLAGLE
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
[UserName] = '{All Users}', 
[UserType] = '{All Users}', 
[DatabaseUserName] = '{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]

8

0

해설 (1)

JamieSee

원본 비 AMP 버전 편집

일부 다른 답을 볼 수 있는 권한을 보유하고 있는 크리스티나랑 않는비즈니스 데이터베이스입니까. 아래 코드는 허가를 얻을 수 있는 첫 번째 쿼리하지 데이터베이스 수준이 아닌 모든 시스템 객체입니다. 해당 it 생성하는지 허가합니다 명령문입니다 잘 알려져 있다. 두 번째 쿼리하지 메버시프스 모든 역할이 된다.

이 때문에 각 데이터베이스에 대해 함께 사용할 수 있지만, 너무 오래 실행하십시오 sp_MSforeachdb. 그렇게 하려면 you& # 39, d 가 추가하기 위해서 마스터 데이터베이스 시스템인 저장 프로시저.

또한 모든 가능성을 다룰 수 있는 스크립트입니다 you&; d # 39 에서 서버 수준 권한이 있어야 한다.

SELECT DB_NAME() AS database_name
    , class
    , class_desc
    , major_id
    , minor_id
    , grantee_principal_id
    , grantor_principal_id
    , databasepermissions.type
    , permission_name
    , STATE
    , state_desc
    , granteedatabaseprincipal.name AS grantee_name
    , granteedatabaseprincipal.type_desc AS grantee_type_desc
    , granteeserverprincipal.name AS grantee_principal_name
    , granteeserverprincipal.type_desc AS grantee_principal_type_desc
    , grantor.name AS grantor_name
    , granted_on_name
    , permissionstatement + N' TO ' + QUOTENAME(granteedatabaseprincipal.name) + CASE 
        WHEN STATE = N'W'
            THEN N' WITH GRANT OPTION'
        ELSE N''
        END AS permissionstatement
FROM (
    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(CONVERT(NVARCHAR(MAX), DB_NAME())) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permissionstatement
    FROM sys.database_permissions
    WHERE (sys.database_permissions.class = 0)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + QUOTENAME(sys.schemas.name) + N'.' + QUOTENAME(sys.objects.name) + COALESCE(N' (' + QUOTENAME(sys.columns.name) + N')', N'') AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.objects
        ON sys.objects.object_id = sys.database_permissions.major_id
    INNER JOIN sys.schemas
        ON sys.schemas.schema_id = sys.objects.schema_id
    LEFT OUTER JOIN sys.columns
        ON sys.columns.object_id = sys.database_permissions.major_id
            AND sys.columns.column_id = sys.database_permissions.minor_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 1)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.schemas.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SCHEMA::' + QUOTENAME(sys.schemas.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.schemas
        ON sys.schemas.schema_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 3)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(targetPrincipal.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ' + targetPrincipal.type_desc + N'::' + QUOTENAME(targetPrincipal.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.database_principals AS targetPrincipal
        ON targetPrincipal.principal_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 4)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.assemblies.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASSEMBLY::' + QUOTENAME(sys.assemblies.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.assemblies
        ON sys.assemblies.assembly_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 5)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.types.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.types
        ON sys.types.user_type_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 6)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.types.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON TYPE::' + QUOTENAME(sys.types.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.types
        ON sys.types.user_type_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 6)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.xml_schema_collections.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON XML SCHEMA COLLECTION::' + QUOTENAME(sys.xml_schema_collections.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.xml_schema_collections
        ON sys.xml_schema_collections.xml_collection_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 10)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON MESSAGE TYPE::' + QUOTENAME(sys.service_message_types.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.service_message_types
        ON sys.service_message_types.message_type_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 15)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CONTRACT::' + QUOTENAME(sys.service_contracts.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.service_contracts
        ON sys.service_contracts.service_contract_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 16)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON SERVICE::' + QUOTENAME(sys.services.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.services
        ON sys.services.service_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 17)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON REMOTE SERVICE BINDING::' + QUOTENAME(sys.remote_service_bindings.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.remote_service_bindings
        ON sys.remote_service_bindings.remote_service_binding_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 18)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ROUTE::' + QUOTENAME(sys.routes.name COLLATE SQL_Latin1_General_CP1_CI_AS) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.routes
        ON sys.routes.route_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 19)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.symmetric_keys.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.symmetric_keys.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.symmetric_keys
        ON sys.symmetric_keys.symmetric_key_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 24)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.certificates.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON CERTIFICATE::' + QUOTENAME(sys.certificates.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.certificates
        ON sys.certificates.certificate_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 25)

    UNION ALL

    SELECT sys.database_permissions.class
        , sys.database_permissions.class_desc
        , sys.database_permissions.major_id
        , sys.database_permissions.minor_id
        , sys.database_permissions.grantee_principal_id
        , sys.database_permissions.grantor_principal_id
        , sys.database_permissions.type
        , sys.database_permissions.permission_name
        , sys.database_permissions.state
        , sys.database_permissions.state_desc
        , QUOTENAME(sys.asymmetric_keys.name) AS granted_on_name
        , CASE 
            WHEN sys.database_permissions.state = N'W'
                THEN N'GRANT'
            ELSE sys.database_permissions.state_desc
            END + N' ' + sys.database_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS + N' ON ASYMMETRIC KEY::' + QUOTENAME(sys.asymmetric_keys.name) AS permissionstatement
    FROM sys.database_permissions
    INNER JOIN sys.asymmetric_keys
        ON sys.asymmetric_keys.asymmetric_key_id = sys.database_permissions.major_id
    WHERE (sys.database_permissions.major_id >= 0)
        AND (sys.database_permissions.class = 26)
    ) AS databasepermissions
INNER JOIN sys.database_principals AS granteedatabaseprincipal
    ON granteedatabaseprincipal.principal_id = grantee_principal_id
LEFT OUTER JOIN sys.server_principals AS granteeserverprincipal
    ON granteeserverprincipal.sid = granteedatabaseprincipal.sid
INNER JOIN sys.database_principals AS grantor
    ON grantor.principal_id = grantor_principal_id
ORDER BY grantee_name, granted_on_name

SELECT roles.name AS role_name
    , roles.principal_id
    , roles.type AS role_type
    , roles.type_desc AS role_type_desc
    , roles.is_fixed_role AS role_is_fixed_role
    , memberdatabaseprincipal.name AS member_name
    , memberdatabaseprincipal.principal_id AS member_principal_id
    , memberdatabaseprincipal.type AS member_type
    , memberdatabaseprincipal.type_desc AS member_type_desc
    , memberdatabaseprincipal.is_fixed_role AS member_is_fixed_role
    , memberserverprincipal.name AS member_principal_name
    , memberserverprincipal.type_desc member_principal_type_desc
    , N'ALTER ROLE ' + QUOTENAME(roles.name) + N' ADD MEMBER ' + QUOTENAME(memberdatabaseprincipal.name) AS AddRoleMembersStatement
FROM sys.database_principals AS roles
INNER JOIN sys.database_role_members
    ON sys.database_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.database_principals AS memberdatabaseprincipal
    ON memberdatabaseprincipal.principal_id = sys.database_role_members.member_principal_id
LEFT OUTER JOIN sys.server_principals AS memberserverprincipal
    ON memberserverprincipal.sid = memberdatabaseprincipal.sid
ORDER BY role_name
    , member_name

JamieSee

편집된 답변22일 5월 2017 в 9:31

7

0

해설 (0)

kiran nellimarla

원본 비 AMP 버전 편집

CREATE PROCEDURE Get_permission 
AS 
    DECLARE @db_name  VARCHAR(200), 
            @sql_text VARCHAR(max) 

    SET @sql_text='Create table ##db_name (user_name varchar(max),' 

    DECLARE db_cursor CURSOR FOR 
      SELECT name 
      FROM   sys.databases 

    OPEN db_cursor 

    FETCH next FROM db_cursor INTO @db_name 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          SET @sql_text=@sql_text + @db_name + ' varchar(max),' 

          FETCH next FROM db_cursor INTO @db_name 
      END 

    CLOSE db_cursor 

    SET @sql_text=@sql_text + 'Server_perm varchar(max))' 

    EXEC (@sql_text) 

    DEALLOCATE db_cursor 

    DECLARE @RoleName VARCHAR(50) 
    DECLARE @UserName VARCHAR(50) 
    DECLARE @CMD VARCHAR(1000) 

    CREATE TABLE #permission 
      ( 
         user_name    VARCHAR(50), 
         databasename VARCHAR(50), 
         role         VARCHAR(50) 
      ) 

    DECLARE longspcur CURSOR FOR 
      SELECT name 
      FROM   sys.server_principals 
      WHERE  type IN ( 'S', 'U', 'G' ) 
             AND principal_id > 4 
             AND name NOT LIKE '##%' 
             AND name  'NT AUTHORITY\SYSTEM' 
             AND name  'ONDEMAND\Administrator' 
             AND name NOT LIKE 'steel%' 

    OPEN longspcur 

    FETCH next FROM longspcur INTO @UserName 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          CREATE TABLE #userroles_kk 
            ( 
               databasename VARCHAR(50), 
               role         VARCHAR(50) 
            ) 

          CREATE TABLE #rolemember_kk 
            ( 
               dbrole     VARCHAR(100), 
               membername VARCHAR(100), 
               membersid  VARBINARY(2048) 
            ) 

          SET @CMD = 'use ? truncate table #RoleMember_kk insert into #RoleMember_kk exec sp_helprolemember  insert into #UserRoles_kk (DatabaseName, Role) select db_name(), dbRole from #RoleMember_kk where MemberName = ''' + @UserName + '''' 

          EXEC Sp_msforeachdb 
            @CMD 

          INSERT INTO #permission 
          SELECT @UserName 'user', 
                 b.name, 
                 u.role 
          FROM   sys.sysdatabases b 
                 LEFT OUTER JOIN #userroles_kk u 
                              ON u.databasename = b.name --and u.Role='db_owner' 
          ORDER  BY 1 

          DROP TABLE #userroles_kk; 

          DROP TABLE #rolemember_kk; 

          FETCH next FROM longspcur INTO @UserName 
      END 

    CLOSE longspcur 

    DEALLOCATE longspcur 

    TRUNCATE TABLE ##db_name 

    DECLARE @d1 VARCHAR(max), 
            @d2 VARCHAR(max), 
            @d3 VARCHAR(max), 
            @ss VARCHAR(max) 
    DECLARE perm_cur CURSOR FOR 
      SELECT * 
      FROM   #permission 
      ORDER  BY 2 DESC 

    OPEN perm_cur 

    FETCH next FROM perm_cur INTO @d1, @d2, @d3 

    WHILE @@FETCH_STATUS = 0 
      BEGIN 
          IF NOT EXISTS(SELECT 1 
                        FROM   ##db_name 
                        WHERE  user_name = @d1) 
            BEGIN 
                SET @ss='insert into ##db_name(user_name) values (''' 
                        + @d1 + ''')' 

                EXEC (@ss) 

                SET @ss='update ##db_name set ' + @d2 + '=''' + @d3 
                        + ''' where user_name=''' + @d1 + '''' 

                EXEC (@ss) 
            END 
          ELSE 
            BEGIN 
                DECLARE @var            NVARCHAR(max), 
                        @ParmDefinition NVARCHAR(max), 
                        @var1           NVARCHAR(max) 

                SET @var = N'select @var1=' + @d2 
                           + ' from ##db_name where USER_NAME=''' + @d1 
                           + ''''; 
                SET @ParmDefinition = N'@var1 nvarchar(300) OUTPUT'; 

                EXECUTE Sp_executesql 
                  @var, 
                  @ParmDefinition, 
                  @var1=@var1 output; 

                SET @var1=Isnull(@var1, ' ') 
                SET @var= '  update ##db_name set ' + @d2 + '=''' + @var1 + ' ' 
                          + @d3 + ''' where user_name=''' + @d1 + '''  ' 

                EXEC (@var) 
            END 

          FETCH next FROM perm_cur INTO @d1, @d2, @d3 
      END 

    CLOSE perm_cur 

    DEALLOCATE perm_cur 

    SELECT * 
    FROM   ##db_name 

    DROP TABLE ##db_name 

    DROP TABLE #permission

Taryn

편집된 답변7일 3월 2013 в 12:42

5

0

해설 (1)

Ulysses

원본 비 AMP 버전 편집

하지만 이 의미하는 것이며, 그 위에 优秀 Sp_msforeachdb 제페르미시언스 저장 프로시저를 사용할 경우 포함하는 모든 데이터베이스뿐 이름을 SQL 인스턴스에는 분할합니다 공백이나 대시 및 기타 비사양 best practice 자입니다. 또한 열 Sp_msforeachdb 사용을 허용하지 않는 내가 hotenough. 버전 2 개 포함 1 - 로그인 계정일 나타내는 경우 시스템 로그인 (이시자드민로긴) 과 2 는 고아 같다니요 경우 - 로그인 사용자 (이왕티로프).

USE [master] ;
GO
IF EXISTS
(
    SELECT * FROM sys.objects
    WHERE object_id = OBJECT_ID(N'dbo.uspGetPermissionsOfAllLogins_DBsOnColumns')
    AND [type] in (N'P',N'PC')
)
BEGIN
    DROP PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
END
GO
CREATE PROCEDURE dbo.uspGetPermissionsOfAllLogins_DBsOnColumns
AS
SET NOCOUNT ON
;
BEGIN TRY
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#permission]')
    )
    DROP TABLE #permission
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#userroles_kk]')
    )
    DROP TABLE #userroles_kk
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[#rolemember_kk]')
    )
    DROP TABLE #rolemember_kk
    ;
    IF EXISTS
    (
        SELECT * FROM tempdb.dbo.sysobjects
        WHERE id = object_id(N'[tempdb].dbo.[##db_name]')
    )
    DROP TABLE ##db_name
    ;
    DECLARE
    @db_name VARCHAR(255)
    ,@sql_text VARCHAR(MAX) 
    ;
    SET @sql_text =
    'CREATE TABLE ##db_name
    (
        LoginUserName VARCHAR(MAX)
        ,' 
    ;
    DECLARE cursDBs CURSOR FOR 
        SELECT [name]
        FROM sys.databases
        ORDER BY [name]
    ;
    OPEN cursDBs 
    ;
    FETCH NEXT FROM cursDBs INTO @db_name 
    WHILE @@FETCH_STATUS = 0 
        BEGIN 
                SET @sql_text =
        @sql_text + QUOTENAME(@db_name) + ' VARCHAR(MAX)
        ,' 
                FETCH NEXT FROM cursDBs INTO @db_name 
        END 
    CLOSE cursDBs 
    ;
    SET @sql_text =
        @sql_text + 'IsSysAdminLogin CHAR(1)
        ,IsEmptyRow CHAR(1)
    )' 

    --PRINT @sql_text
    EXEC (@sql_text)
    ;
    DEALLOCATE cursDBs 
    ;
    DECLARE
    @RoleName VARCHAR(255) 
    ,@UserName VARCHAR(255) 
    ;
    CREATE TABLE #permission 
    (
     LoginUserName VARCHAR(255)
     ,databasename VARCHAR(255)
     ,[role] VARCHAR(255)
    ) 
    ;
    DECLARE cursSysSrvPrinName CURSOR FOR 
        SELECT [name]
        FROM sys.server_principals 
        WHERE
        [type] IN ( 'S', 'U', 'G' )
        AND principal_id > 4
        AND [name] NOT LIKE '##%'
        ORDER BY [name]
    ;
    OPEN cursSysSrvPrinName
    ;
    FETCH NEXT FROM cursSysSrvPrinName INTO @UserName 
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        CREATE TABLE #userroles_kk 
        ( 
             databasename VARCHAR(255)
             ,[role] VARCHAR(255)
        ) 
        ;
        CREATE TABLE #rolemember_kk 
        ( 
             dbrole VARCHAR(255)
             ,membername VARCHAR(255)
             ,membersid VARBINARY(2048)
        ) 
        ;
        DECLARE cursDatabases CURSOR FAST_FORWARD LOCAL FOR
        SELECT [name]
        FROM sys.databases
        ORDER BY [name]
        ;
        OPEN cursDatabases
        ;
        DECLARE 
        @DBN VARCHAR(255)
        ,@sqlText NVARCHAR(4000)
        ;
        FETCH NEXT FROM cursDatabases INTO @DBN
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sqlText =
    N'USE ' + QUOTENAME(@DBN) + ';
    TRUNCATE TABLE #RoleMember_kk 
    INSERT INTO #RoleMember_kk 
    EXEC sp_helprolemember 
    INSERT INTO #UserRoles_kk
    (DatabaseName,[Role])
    SELECT db_name(),dbRole
    FROM #RoleMember_kk
    WHERE MemberName = ''' + @UserName + '''
    '

            --PRINT @sqlText ;
            EXEC sp_executesql @sqlText ;
        FETCH NEXT FROM cursDatabases INTO @DBN
        END
        CLOSE cursDatabases
        ;
        DEALLOCATE cursDatabases
        ;
        INSERT INTO #permission 
        SELECT
        @UserName 'user'
        ,b.name
        ,u.[role]
        FROM
        sys.sysdatabases b
        LEFT JOIN
        #userroles_kk u 
            ON QUOTENAME(u.databasename) = QUOTENAME(b.name)
        ORDER  BY 1 
        ;
        DROP TABLE #userroles_kk
        ; 
        DROP TABLE #rolemember_kk
        ;
        FETCH NEXT FROM cursSysSrvPrinName INTO @UserName 
    END 
    CLOSE cursSysSrvPrinName 
    ;
    DEALLOCATE cursSysSrvPrinName 
    ;
    TRUNCATE TABLE ##db_name 
    ;
    DECLARE
    @d1 VARCHAR(MAX)
    ,@d2 VARCHAR(MAX)
    ,@d3 VARCHAR(MAX)
    ,@ss VARCHAR(MAX)
    ;
    DECLARE cursPermisTable CURSOR FOR
        SELECT * FROM #permission 
        ORDER BY 2 DESC 
    ;
    OPEN cursPermisTable
    ;
    FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
    WHILE @@FETCH_STATUS = 0 
    BEGIN 
        IF NOT EXISTS
        (
            SELECT 1 FROM ##db_name WHERE LoginUserName = @d1
        )
        BEGIN 
            SET @ss =
            'INSERT INTO ##db_name(LoginUserName) VALUES (''' + @d1 + ''')' 
            EXEC (@ss) 
            ;
            SET @ss =
            'UPDATE ##db_name SET ' + @d2 + ' = ''' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''' 
            EXEC (@ss)
            ;
        END 
        ELSE 
        BEGIN 
            DECLARE
            @var NVARCHAR(MAX)
            ,@ParmDefinition NVARCHAR(MAX)
            ,@var1 NVARCHAR(MAX)
            ;
            SET @var =
            N'SELECT @var1 = ' + QUOTENAME(@d2) + ' FROM ##db_name WHERE LoginUserName = ''' + @d1 + ''''
            ; 
            SET @ParmDefinition =
            N'@var1 NVARCHAR(600) OUTPUT '
            ; 
            EXECUTE Sp_executesql @var,@ParmDefinition,@var1 = @var1 OUTPUT
            ;
            SET @var1 =
            ISNULL(@var1, ' ')
            ;
            SET @var =
            '  UPDATE ##db_name SET ' + @d2 + '=''' + @var1 + ' ' + @d3 + ''' WHERE LoginUserName = ''' + @d1 + '''  '
            ;
            EXEC (@var)
            ;
        END
        FETCH NEXT FROM cursPermisTable INTO @d1,@d2,@d3
    END 
    CLOSE cursPermisTable
    ;
    DEALLOCATE cursPermisTable 
    ;
    UPDATE ##db_name SET
    IsSysAdminLogin = 'Y'
    FROM
    ##db_name TT
    INNER JOIN
    dbo.syslogins SL
        ON TT.LoginUserName = SL.[name]
    WHERE
    SL.sysadmin = 1
    ;
    DECLARE cursDNamesAsColumns CURSOR FAST_FORWARD LOCAL FOR
    SELECT [name]
    FROM tempdb.sys.columns
    WHERE
    OBJECT_ID = OBJECT_ID('tempdb..##db_name')
    AND [name] NOT IN ('LoginUserName','IsEmptyRow')
    ORDER BY [name]
    ;
    OPEN cursDNamesAsColumns
    ;
    DECLARE 
    @ColN VARCHAR(255)
    ,@tSQLText NVARCHAR(4000)
    ;
    FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @tSQLText =
N'UPDATE ##db_name SET
IsEmptyRow = ''N''
WHERE IsEmptyRow IS NULL
AND ' + QUOTENAME(@ColN) + ' IS NOT NULL
;
'

        --PRINT @tSQLText ;
        EXEC sp_executesql @tSQLText ;
    FETCH NEXT FROM cursDNamesAsColumns INTO @ColN
    END
    CLOSE cursDNamesAsColumns
    ;
    DEALLOCATE cursDNamesAsColumns
    ;
    UPDATE ##db_name SET
    IsEmptyRow = 'Y'
    WHERE IsEmptyRow IS NULL
    ;
    UPDATE ##db_name SET
    IsSysAdminLogin = 'N'
    FROM
    ##db_name TT
    INNER JOIN
    dbo.syslogins SL
        ON TT.LoginUserName = SL.[name]
    WHERE
    SL.sysadmin = 0
    ;
    SELECT * FROM ##db_name
    ;
    DROP TABLE ##db_name
    ;
    DROP TABLE #permission
    ;
END TRY
BEGIN CATCH
    DECLARE
    @cursDBs_Status INT
    ,@cursSysSrvPrinName_Status INT
    ,@cursDatabases_Status INT
    ,@cursPermisTable_Status INT
    ,@cursDNamesAsColumns_Status INT
    ;
    SELECT
    @cursDBs_Status = CURSOR_STATUS('GLOBAL','cursDBs')
    ,@cursSysSrvPrinName_Status = CURSOR_STATUS('GLOBAL','cursSysSrvPrinName')
    ,@cursDatabases_Status = CURSOR_STATUS('GLOBAL','cursDatabases')
    ,@cursPermisTable_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
    ,@cursDNamesAsColumns_Status = CURSOR_STATUS('GLOBAL','cursPermisTable')
    ;
    IF @cursDBs_Status > -2
        BEGIN
            CLOSE cursDBs ;
            DEALLOCATE cursDBs ;
        END
    IF @cursSysSrvPrinName_Status > -2
        BEGIN
            CLOSE cursSysSrvPrinName ;
            DEALLOCATE cursSysSrvPrinName ;
        END
    IF @cursDatabases_Status > -2
        BEGIN
            CLOSE cursDatabases ;
            DEALLOCATE cursDatabases ;
        END
    IF @cursPermisTable_Status > -2
        BEGIN
            CLOSE cursPermisTable ;
            DEALLOCATE cursPermisTable ;
        END
    IF @cursDNamesAsColumns_Status > -2
        BEGIN
            CLOSE cursDNamesAsColumns ;
            DEALLOCATE cursDNamesAsColumns ;
        END
    SELECT ErrorNum = ERROR_NUMBER(),ErrorMsg = ERROR_MESSAGE() ;
END CATCH
GO
/*
EXEC [master].dbo.uspGetPermissionsOfAllLogins_DBsOnColumns ;
*/

5

0

해설 (2)

John Zabroski

원본 비 AMP 버전 편집

이것은 내 다른 버전, 발췌. 난 이제 막 내가 어떻게 하는 데 30 분 @Jeremy & # 39 의 오토메이티드 너희에게베풀어진 한국증권선물거래소법을 및 핵심 것 같다 "는 말을 들었다. # 39, t, s # 39 라고 내가 didn& 업데이트하려면 Jeremy& 버그, 그래서 나는 내 혹시 무슨 글을 여기에 버전을 선보였다. 내가 페어링할 제안하세요 전체 스크립트입니다 일부 영감 (靈感) 에서 발췌 # 39 의 케네스 fisher& t-sql 화요일: 어떤 특정 사용자 권한 수행합니까 %s/dbase/ext_table.?: 이렇게 하면 감사 질문 / 답변 준수에서 상향식 반대되는 개념으로, 하향식 (top-down). "' & # 39, & # 39 로 로그인 = 실행하십시오 &lt loginname>;;

Token.name 로 groupname 선택 충스러웠으니 sys.login_token 토큰인지 Bigadmin sys.server_principals grp 켜짐이 토켄드사이드 = 그레피사이드

39, & # 39, WINDOWS GROUP& [type] = 여기서 토큰인지.

39, & # 39, [type] = G& 및 grp.

복구입니다. "' Ad 그룹 멤버 ',' Windows '이 닫히지만 고려해보십시오 Contoso\DB_AdventureWorks_Accounting 이해할 수 있는 Contoso\John.Doe'. '인증' 를 통해 존스도 장방튀르부르크스 server_principal Contoso\DB_AdventureWorks_Logins indows AD 그룹. 누군가 어떤 권한을 갖고 &quot 존스도 높여줍니까 &quot 묻습니다, 그 질문을 부트해 답할 수 없는 경우, 아래 스크립트입니다.? 그 아래 각 행의 반복할 데 필요한 힘을 redhat. 의해 반환되었습니다 스크립트입니다 위의 스크립트입니다. (할 수도 있습니다 정상화를 위한 오래되었는가 '이름' 값을 찾는 통해 사용자의 SID 의 Active Directory 제공업체입니다.) 다음은 스크립트입니다 역동기화 조회 없이 같은 것은 없다. "' /*

소스 스크립트입니다 http://moss. dell.: http://stackoverflow.com/a/7059579/1387418 보안 감사 보고서

모든 사용자 또는 windows 사용자 /그룹: 직접 sql 액세스 프로비저닝된 목록
모든 액세스 나열하십시오 sql 데이터베이스 사용자 또는 windows 사용자 또는 응용 프로그램 역할을 통해 /그룹: 프로비저닝된
모든 국민은 역할을 프로비저닝된 액세스하면 목록 열 반환됨: Sername: SQL 또는 Windows / Active Directory 사용자 계정. Active Directory 그룹화할 이 될 수도 있습니다. 우스티페: # 39, & # 39 값을 SQL User& 하나가 됩니다. # 39, & # 39, 또는 Windows User&. 이같은 사용자 유형에 대해 정의된 SQL 서버 사용자 계정. 프린시아플루세르나미: 그 후 sername 다타바세우저나미 경우 sername 비어 있지 프린시펄티페: # 39, & # 39, & # 39, SQL User& 가능한 값은 User& # 39, & # 39, 데이터베이스, Windows, # 39, & # 39, Windows Group& Role& # 39;; 다타바세우저나미: 관련 사용자 이름 데이터베이스 사용자 계정에 정의되어 있다. 데이터베이스 사용자 아닐 수 있습니다 같은 서버 사용자. 역할: 역할 이름을. 이렇게 하면 경우 관련 권한을 정의됩니까 dell. 직접 객체에는 널일 그렇지 않으면 이 사용자 계정의 사용자가 멤버인지 역할의 이름을 될 수 있다. 페르미시온티페: 사용자 / 역할이 객체에 대한 권한 유형. 예를 들어, 실행하십시오 접속하십시오 선택할 수 있습니다 삽입, 삭제, 변경, 제어, 시행하십시오 소유권자를, 뷰 정의가 등. 이 값은 모든 역할에 대한 구성될 수 없습니다. 일부 기능이 내장됨 역할이 더 암시적입니다 사용 권한 정의. 페르미시의 국가: 이런 상태를 권한 부여, 유형, 참조용이므로 eny 등 있을 수 있습니다. 이 값은 모든 역할에 대한 구성될 수 없습니다. 일부 기능이 내장됨 역할이 더 암시적입니다 사용 권한 정의. ObjectType: 객체 유형에 대한 권한만으로 할당되었는지 사용자 / 역할. 예를 들어, USER_TABLE 포함됩니다. , 보기인 sql_scalar_function SQL_INLINE_TABLE_VALUED_FUNCTION SQL_STORED_PROCEDURE 상술합니다. 이 값은 모든 역할에 대한 구성될 수 없습니다. 일부 기능이 내장됨 역할이 더 암시적입니다 사용 권한 정의. 개체 이름: 사용자가 할당되었는지 객체의 이름을 / 역할에 대한 권한만으로. 이 값은 모든 역할에 대한 구성될 수 없습니다. 일부 기능이 내장됨 역할이 더 암시적입니다 사용 권한 정의. 콜럼나메: 사용자가 / 역할에 대한 권한만으로 할당되었는지 열 객체에는 이름입니다. 이 값은 테이블, 뷰 테이블 또는 함수 값을 객체인지 경우에만 입력됩니다. */ 너희가운데 @HideDatabaseDiagrams 비트 = 1;

Sql 액세스 프로비저닝된 모든 사용자 또는 windows 사용자 /그룹: 직접 목록 선택 [Type] [sername] = 케이스 데브친왕.
39, & # 39 S& 때. [성명] - SQL 사용자 어졌다면 데브친왕.

39, & # 39 U& 때. [성명] - Windows 사용자 어졌다면 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

Null else 끝 [Type] [우스티페] = 케이스 데브친왕.

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

[Type] 다른 데브친왕. 끝 [프린시펄우저나미] = 가속 시 가용 ( [Type] 경우 데브친왕.

39, & # 39 S& 때. [성명] - SQL 사용자 어졌다면 데브친왕.

39, & # 39 U& 때. [성명] - Windows 사용자 어졌다면 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

Null else 끝 [성명] 데브친왕. ), [Type] [프린시펄티페] = 케이스 데브친왕.

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. # 39, & # 39, 데이터베이스 Role& 어졌다면

39, & # 39 G& 때. # 39, & # 39, Windows Group& 어졌다면

끝 [성명], [다타바세우저나미] = 데브친왕. [역할] = null, [Permission_name], [페르미시온티페] = 페름. [State_desc], [페르미시의 국가] = 페름. [ObjectType] = obj. [type_desc], [class_desc] - perm. [로비치셰마] = OBJECT_SCHEMA_NAME (perm.major_id), [개체 이름] = OBJECT_NAME (perm.major_id), [성명] [콜럼나메] = 열. 에서
database 사용자 sys.database_principals 데브친왕 왼쪽 참가하십시오
로그인 계정 [sid] = 사친왕. [sid] 에서 sys.server_principals 사친왕 데브친왕. 왼쪽 참가하십시오
사용 권한 페름 sys.database_permissions. [principal_id] 의 [grantee_principal_id] = 데브친왕 페름. 왼쪽 참가하십시오
테이블 열 [object_id] = perm.major_id 시s.콜럼너스 col 켜짐이 col. [Column_id] = 페름. [minor_id] 과 열. 왼쪽 참가하십시오 페름 시스트로비치스 obj = obj. [object_id] 의 [major_id]. 여기서 데브친왕. [type] 에 (& # 39, & # 39 U& # 39 S& # 39;;;;) 및 사례 @Hidedatabasediagrams 때 = 1 이고
39, & # 39, [이름] = guest& 데브친왕.

그리고 ( (

39, & # 39, SQL_SCALAR_FUNCTION& obj.type_desc =

39, & # 39 와 OBJECT_NAME (perm.major_id) = fn_diagramobjects&.

) 또는 (

39, & # 39, SQL_STORED_PROCEDURE& obj.type_desc =

및 OBJECT_NAME (perm.major_id) 였다. (

39 n& sp_alterdiagram& # 39;;

39 n& sp_creatediagram& # 39;;

39 n& sp_dropdiagram& # 39;;

39 n& sp_helpdiagramdefinition& # 39;;

39 n& sp_helpdiagrams& # 39;;

39 n& sp_renamediagram& # 39;;

) ) ) 그럼 0 다른 1 끝 = 1 연합
Sql 액세스 프로비저닝된 모든 사용자 또는 windows 사용자 목록을 통해 /그룹: 데이터베이스 또는 응용 프로그램 역할 선택 [Type] [sername] = 케이스 멤버친왕.
39, & # 39 S& 때. [성명] 다음 멤버친왕.

39, & # 39 U& 때. [성명] 다음 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

Null else 끝 [Type] [우스티페] = 케이스 멤버친왕.

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

끝 [프린시펄우저나미] = 가속 시 가용 ( [Type] 경우 멤버친왕.

39, & # 39 S& 때. [성명] 다음 멤버친왕.

39, & # 39 U& 때. [성명] 다음 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

Null else 끝 [성명] 멤버친왕. ), [Type] [프린시펄티페] = 케이스 멤버친왕.

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. # 39, & # 39, 데이터베이스 Role& 어졌다면

39, & # 39 G& 때. # 39, & # 39, Windows Group& 어졌다면

끝 [성명], [다타바세우저나미] = 멤버친왕. [성명], [역할] = 롤프린스. [Permission_name], [페르미시온티페] = 페름. [State_desc], [페르미시의 국가] = 페름. [Class_desc], [ObjectType] = - perm obj.type_desc. [로비치셰마] = OBJECT_SCHEMA_NAME (perm.major_id), [개체 이름] = OBJECT_NAME (perm.major_id), [성명] [콜럼나메] = 열. 에서
역할 / 협회 sys.database_role_members 멤버 참가하십시오
역할 sys.database_principals 롤프린스 롤프린스. [principal_id] = [role_principal_id] 의 멤버. 참가하십시오
역할 회원 (데이터베이스 사용자) sys.database_principals 멤버친왕 멤버친왕. [principal_id] = [member_principal_id] 의 멤버. 왼쪽 참가하십시오
로그인 계정 [sid] = 사친왕. [sid] 에서 sys.server_principals 사친왕 멤버친왕. 왼쪽 참가하십시오
사용 권한 페름 sys.database_permissions. [principal_id] 의 [grantee_principal_id] = 롤프린스 페름. 왼쪽 참가하십시오
테이블 열 [object_id] = perm.major_id 시s.콜럼너스 col 켜짐이 col. [Column_id] = 페름. [minor_id] 과 열. 왼쪽 참가하십시오 페름 시스트로비치스 obj = obj. [object_id] 의 [major_id]. 여기서 케이스 @Hidedatabasediagrams 때 = 1 이고
39, & # 39, [이름] = guest& 멤버친왕.

그리고 ( (

39, & # 39, SQL_SCALAR_FUNCTION& obj.type_desc =

39, & # 39 와 OBJECT_NAME (perm.major_id) = fn_diagramobjects&.

) 또는 (

39, & # 39, SQL_STORED_PROCEDURE& obj.type_desc =

및 OBJECT_NAME (perm.major_id) 였다. (

39 n& sp_alterdiagram& # 39;;

39 n& sp_creatediagram& # 39;;

39 n& sp_dropdiagram& # 39;;

39 n& sp_helpdiagramdefinition& # 39;;

39 n& sp_helpdiagrams& # 39;;

39 n& sp_renamediagram& # 39;;

) ) ) 그럼 0 다른 1 끝 = 1 연합
모든 국민이 얻을 수 있는 역할, 기본적으로 모두라뇨 프로비저닝할 액세스하면 목록 선택 & # 39, & # 39 모든 사용자가 [sername] = {}; & # 39, & # 39 모든 사용자가 [우스티페] = {}; & # 39, & # 39 모든 사용자가 [프린시펄우저나미] = {}; & # 39, & # 39 모든 사용자가 [프린시펄티페] = {}; & # 39, & # 39 모든 사용자가 [다타바세우저나미] = {}; [성명], [역할] = 롤프린스. [Permission_name], [페르미시온티페] = 페름. [State_desc], [페르미시의 국가] = 페름. [Class_desc], [ObjectType] = - perm obj.type_desc. [로비치셰마] = OBJECT_SCHEMA_NAME (perm.major_id), [개체 이름] = OBJECT_NAME (perm.major_id), [성명] [콜럼나메] = 열. 에서
역할 sys.database_principals 롤프린스 왼쪽 참가하십시오
권한만으로 역할 페름 sys.database_permissions. [principal_id] 의 [grantee_principal_id] = 롤프린스 페름. 왼쪽 참가하십시오
테이블 열 [object_id] = perm.major_id 시s.콜럼너스 col 켜짐이 col. [Column_id] = 페름. [minor_id] 과 열. 참가하십시오
모든 개체가 페름. [major_id] 의 [object_id] = obj 시스트로비치스 obj. 여기서
역할만
39, & # 39, [type] = R& 롤프린스. 및
단 공용 역할이
39, & # 39, [이름] = public& 롤프린스. 및
우리 오브젝트만 밀리초입니다 객체에는 아닌 obj.is_ms_shipped = 0 및 사례 @Hidedatabasediagrams 때 = 1 이고
39, & # 39, [이름] = public& 롤프린스.

그리고 ( (

39, & # 39, SQL_SCALAR_FUNCTION& obj.type_desc =

39, & # 39 와 OBJECT_NAME (perm.major_id) = fn_diagramobjects&.

) 또는 (

39, & # 39, SQL_STORED_PROCEDURE& obj.type_desc =

및 OBJECT_NAME (perm.major_id) 였다. (

39 n& sp_alterdiagram& # 39;;

39 n& sp_creatediagram& # 39;;

39 n& sp_dropdiagram& # 39;;

39 n& sp_helpdiagramdefinition& # 39;;

39 n& sp_helpdiagrams& # 39;;

39 n& sp_renamediagram& # 39;;

) ) ) 그럼 0 다른 1 끝 = 1 ORDER BY [성명], 데브친왕. OBJECT_NAME (perm.major_id), [성명], 열. [permission_name], 페름. [state_desc], 페름. obj.type_desc-perm. [class_desc] "'

John Zabroski

편집된 답변26일 2월 2019 в 6:40

5

0

해설 (0)

Konstantin Taranov

원본 비 AMP 버전 편집

멋진 멋지구리해요 주셔서 감사 스크립트입니다.

난 매우 뛰어난 사용자 사용 추천합니까 감사를 케네스 피셔 ([b] (http://sqlstudies.com/) [t] (https://twitter.com/sqlstudent144)) 저장 프로시저:

[sp_DBPermissions] (https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_DBPermissions.sql)
[sp_SrvPermissions] (https://github.com/sqlstudent144/SQL-Server-Scripts/blob/master/sp_SrvPermissions.sql)

Konstantin Taranov

편집된 답변3일 6월 2019 в 12:46

1

0

해설 (3)

Greg Sipes

원본 비 AMP 버전 편집

나는 이 모든 사실을 알고 있지만 나는 그저 한 명이 실종됐다고 빠르게 일부, 특히 sysadmin 특징이다. # 39 에서 good t look like that, 구멍을 가진 won& 당사의 감사, 이게 내가 지었지

USE master
GO

SELECT DISTINCT 
        p.name AS [loginname] ,
        --p.type,
        p.type_desc ,
        p.is_disabled,
        s.sysadmin,
        sp.permission_name
FROM sys.server_principals p
INNER JOIN sys.syslogins s ON p.sid = s.sid
INNER JOIN sys.server_permissions sp ON p.principal_id = sp.grantee_principal_id
WHERE p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
    -- Logins that are not process logins
    AND p.name NOT LIKE '##%'
ORDER BY p.name
GO

0

해설 (0)

Bugmeister

원본 비 AMP 버전 편집

내가 couldn& 죄송합니다. t # 39, 명성, 하지만 난 이미 대해 숀 로즈 게시물로의 부족으로 인해 개정 &quot public"; # 39 스키마에는 범위지정 보이기 때문에, t 스크립트입니다 역할 부분을 didn& 권한만으로 포지셔닝하여 시스트로비치스트 참가하십시오 인해 (내부) 변경된 후 개정을 위해 추가적으로 where 절 로직을 생략하십시오 시스템 객체여야 왼쪽 참가하십시오 했다. 다음은 파마 내 쿼리하지 주장하고 있다.

--3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        @@servername ServerName
        , db_name() DatabaseName
        , [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND isnull(obj.[is_ms_shipped], 0) = 0
        AND isnull(object_schema_name(perm.[major_id]), '')  'sys'

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

0

해설 (0)

Jeremy · Accepted Answer · 2011-08-14T20:39:15+00:00

이는 당시 기준으로 크랙 (crack my first 쿼리하지 andomar& # 39 의 제안. 이 질의에서는 고안되었습니다 사용자가 직접 적용할 수 있는 권한 목록을 제공하십시오 인컨텍스트 사용자 계정을 통해, 또는 관리/역할 사용자가 있다.

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [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
    sys.login_token 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  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [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.login_token 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  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{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]

SQL Server / 모든 사용자에 대한 모든 권한을 com/go/downloads_kr 쿼리하지 데이터베이스에 액세스

39, t 에 대한 의견 수락됨 오토메이티드 그러하매 I& can& # 39, ll 추가 일부 설명을 있습니다.

39 의 it& 굈 '내 경험 있는 것이 아니라' 표시 '을 (를)' sys.server_principals sys.login_token 않을 뿐만 아니라 또한 sql 로그인을 indows 수준들과

39, & # 39 G& 반드시 1 명 추가 ',' 를 사용할 수 있도록 indows 그룹 주요 유형

39, ll 게시물로의 i& 첫 부분을 수정, 변경해야 할 뿐만 아니라 다른 모든 부품이 함께 스크립트입니다 제안했다.

39, & # 39, WINDOWS GROUP& [type] = 여기서 토큰인지.

39, & # 39, [type] = G& 및 grp.

39, & # 39 S& 때. [성명] - SQL 사용자 어졌다면 데브친왕.

39, & # 39 U& 때. [성명] - Windows 사용자 어졌다면 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

39, & # 39 S& 때. [성명] - SQL 사용자 어졌다면 데브친왕.

39, & # 39 U& 때. [성명] - Windows 사용자 어졌다면 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. # 39, & # 39, 데이터베이스 Role& 어졌다면

39, & # 39 G& 때. # 39, & # 39, Windows Group& 어졌다면

39, & # 39, [이름] = guest& 데브친왕.

39, & # 39, SQL_SCALAR_FUNCTION& obj.type_desc =

39, & # 39 와 OBJECT_NAME (perm.major_id) = fn_diagramobjects&.

39, & # 39, SQL_STORED_PROCEDURE& obj.type_desc =

39 n& sp_alterdiagram& # 39;;

39 n& sp_creatediagram& # 39;;

39 n& sp_dropdiagram& # 39;;

39 n& sp_helpdiagramdefinition& # 39;;

39 n& sp_helpdiagrams& # 39;;

39 n& sp_renamediagram& # 39;;

39, & # 39 S& 때. [성명] 다음 멤버친왕.

39, & # 39 U& 때. [성명] 다음 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

39, & # 39 S& 때. [성명] 다음 멤버친왕.

39, & # 39 U& 때. [성명] 다음 사친왕.

39, & # 39 R& 때. 그럼 nulll - 데이터베이스 역할

39, & # 39 G& 때. 그럼 nulll - windows7* 그룹

39, & # 39 S& 때. # 39, & # 39, SQL User& 어졌다면

39, & # 39 U& 때. # 39, & # 39, Windows User& 어졌다면

39, & # 39 R& 때. # 39, & # 39, 데이터베이스 Role& 어졌다면

39, & # 39 G& 때. # 39, & # 39, Windows Group& 어졌다면

39, & # 39, [이름] = guest& 멤버친왕.

39, & # 39, SQL_SCALAR_FUNCTION& obj.type_desc =

39, & # 39 와 OBJECT_NAME (perm.major_id) = fn_diagramobjects&.

39, & # 39, SQL_STORED_PROCEDURE& obj.type_desc =

39 n& sp_alterdiagram& # 39;;

39 n& sp_creatediagram& # 39;;

39 n& sp_dropdiagram& # 39;;

39 n& sp_helpdiagramdefinition& # 39;;

39 n& sp_helpdiagrams& # 39;;

39 n& sp_renamediagram& # 39;;

39, & # 39, [type] = R& 롤프린스. 및

39, & # 39, [이름] = public& 롤프린스. 및

39, & # 39, [이름] = public& 롤프린스.

39, & # 39, SQL_SCALAR_FUNCTION& obj.type_desc =

39, & # 39 와 OBJECT_NAME (perm.major_id) = fn_diagramobjects&.

39, & # 39, SQL_STORED_PROCEDURE& obj.type_desc =

39 n& sp_alterdiagram& # 39;;

39 n& sp_creatediagram& # 39;;

39 n& sp_dropdiagram& # 39;;

39 n& sp_helpdiagramdefinition& # 39;;

39 n& sp_helpdiagrams& # 39;;

39 n& sp_renamediagram& # 39;;