블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (279)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total73,242
Today17
Yesterday56

달력

« » 2014.10
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  

공지사항

http://www.sqlskills.com/blogs/paul/how-to-download-a-sqlservr-pdb-symbol-file/

를 읽어보고 wdk 를 설치한다.

모르면 모든 dll 과 exe 파일의 symbol 을 다운로드 하는게 정신건강에 좋단다.

 

1. c:\symbols 폴더를 만든다.

 

2. 폴더에 들어간다

cd "C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn"

 

3. 다음 명령어를 이용해 필요한 파일을 다운로드 한다.

"C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64\symchk" sqlservr.exe /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols
"C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64\symchk" sqldk.dll /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols
"C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64\symchk" sqllang.dll /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols
"C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64\symchk" sqlmin.dll /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols
"C:\Program Files (x86)\Windows Kits\8.1\Debuggers\x64\symchk" sqlboot.dll /s SRV*c:\symbols*http://msdl.microsoft.com/download/symbols

 

4. 실행 경로에 pdb 파일만 복사해 넣는다. (일일이 폴더 들어가서 파일을 골라서 옮긴다)

C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Binn

 

5. 출력해본다.

DBCC TRACEON (3656, -1)


SELECT n.query('.') AS callstack
FROM
(
    SELECT CAST(target_data as xml)
    FROM sys.dm_xe_sessions AS s
    INNER JOIN sys.dm_xe_session_targets AS t
        ON s.address = t.event_session_address
    WHERE s.name = 'system_health'
      AND t.target_name = 'ring_buffer'
) AS src (target_data)
CROSS APPLY target_data.nodes('RingBufferTarget/event/action[@name="callstack"]') as q(n)

 

6. 결과

<action name="callstack" package="package0">
  <type name="callstack" package="package0" />
  <value>XeSosPkg::wait_info::Publish+138 [ @ 0+0x0
SOS_Scheduler::UpdateWaitTimeStats+30c [ @ 0+0x0
SOS_Task::PostWait+90 [ @ 0+0x0
EventInternal&lt;SuspendQueueSLock&gt;::Wait+1f9 [ @ 0+0x0
LockOwner::Sleep+495 [ @ 0+0x0
lck_lockInternal+ebd [ @ 0+0x0
MDL::LockObjectLocal+443 [ @ 0+0x0
SMD::LockObjectAndCheckVersion+86 [ @ 0+0x0
CRangeObject::XretSchemaChanged+430 [ @ 0+0x0
CRangeTable::XretSchemaChanged+1f [ @ 0+0x0
CEnvCollection::XretSchemaChanged+e1 [ @ 0+0x0
CXStmtQuery::XretSchemaChanged+11c [ @ 0+0x0
CXStmtSelect::XretExecute+1be [ @ 0+0x0
CMsqlExecContext::ExecuteStmts&lt;1,1&gt;+400 [ @ 0+0x0
CMsqlExecContext::FExecute+a33 [ @ 0+0x0
CSQLSource::Execute+866 [ @ 0+0x0
process_request+73c [ @ 0+0x0
process_commands+51c [ @ 0+0x0
SOS_Task::Param::Execute+21e [ @ 0+0x0
SOS_Scheduler::RunTask+a8 [ @ 0+0x0
SOS_Scheduler::ProcessTasks+29a [ @ 0+0x0
SchedulerManager::WorkerEntryPoint+261 [ @ 0+0x0
SystemThread::RunWorker+8f [ @ 0+0x0
SystemThreadDispatcher::ProcessWorker+372 [ @ 0+0x0</value>
</action>

 

7. 이제 원하는 콜스택정보를 확인할 수 있다.

저작자 표시 비영리 변경 금지
Posted by 보미아빠

sqltag book 2

분류없음 / 2014/10/21 14:58

 

드디어 출간 되었습니다.

http://www.kyobobook.co.kr/product/detailViewKor.laf?ejkGb=KOR&mallGb=KOR&barcode=9791195328307&orderClick=LAG&Kc=

저작자 표시 비영리 변경 금지
Posted by 보미아빠

http://www.sql-server-performance.com/2002/object-permission-scripts/#

이분들 장인급 이네요 장인....존!경!


-- 1 user 권한 스크립팅 하기

-- 아래에서 testuser 만 replace 한다


DECLARE @DatabaseUserName [sysname]

SET @DatabaseUserName = 'testuser'


SET NOCOUNT ON

DECLARE

@errStatement [varchar](8000),

@msgStatement [varchar](8000),

@DatabaseUserID [smallint],

@ServerUserName [sysname],

@RoleName [varchar](8000),

@ObjectID [int],

@ObjectName [varchar](261)


SELECT

@DatabaseUserID = [sysusers].[uid],

@ServerUserName = [master].[dbo].[syslogins].[loginname]

FROM [dbo].[sysusers]

INNER JOIN [master].[dbo].[syslogins]

ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]

WHERE [sysusers].[name] = @DatabaseUserName

IF @DatabaseUserID IS NULL

BEGIN

SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +

'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'

RAISERROR(@errStatement, 16, 1)

END

ELSE

BEGIN

SET @msgStatement = '-Security creation script for user ' + @ServerUserName + CHAR(13) +

'-Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +

'-Created By: ' + SUSER_NAME() + CHAR(13) +

'-Add User To Database' + CHAR(13) +

'USE [' + DB_NAME() + ']' + CHAR(13) +

'EXEC [sp_grantdbaccess]' + CHAR(13) +

CHAR(9) + '@loginame = ''' + @ServerUserName + ''',' + CHAR(13) +

CHAR(9) + '@name_in_db = ''' + @DatabaseUserName + '''' + CHAR(13) +

'GO' + CHAR(13) +

'-Add User To Roles'

PRINT @msgStatement

DECLARE _sysusers

CURSOR

LOCAL

FORWARD_ONLY

READ_ONLY

FOR

SELECT

[name]

FROM [dbo].[sysusers]

WHERE

[uid] IN

(

SELECT

[groupuid]

FROM [dbo].[sysmembers]

WHERE [memberuid] = @DatabaseUserID

)

OPEN _sysusers

FETCH

NEXT

FROM _sysusers

INTO @RoleName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +

CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +

CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''

PRINT @msgStatement

FETCH

NEXT

FROM _sysusers

INTO @RoleName

END

SET @msgStatement = 'GO' + CHAR(13) +

'-Set Object Specific Permissions'

PRINT @msgStatement

DECLARE _sysobjects

CURSOR

LOCAL

FORWARD_ONLY

READ_ONLY

FOR

SELECT

DISTINCT([sysobjects].[id]),

'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'

FROM [dbo].[sysprotects]

INNER JOIN [dbo].[sysobjects]

ON [sysprotects].[id] = [sysobjects].[id]

WHERE [sysprotects].[uid] = @DatabaseUserID

OPEN _sysobjects

FETCH

NEXT

FROM _sysobjects

INTO

@ObjectID,

@ObjectName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msgStatement = ''

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'SELECT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'INSERT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'UPDATE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'DELETE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'EXECUTE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'REFERENCES,'

IF LEN(@msgStatement) > 0

BEGIN

IF RIGHT(@msgStatement, 1) = ','

SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)

SET @msgStatement = 'GRANT' + CHAR(13) +

CHAR(9) + @msgStatement + CHAR(13) +

CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

CHAR(9) + 'TO ' + @DatabaseUserName

PRINT @msgStatement

END

SET @msgStatement = ''

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'SELECT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'INSERT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'UPDATE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'DELETE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'EXECUTE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'REFERENCES,'

IF LEN(@msgStatement) > 0

BEGIN

IF RIGHT(@msgStatement, 1) = ','

SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)

SET @msgStatement = 'DENY' + CHAR(13) +

CHAR(9) + @msgStatement + CHAR(13) +

CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

CHAR(9) + 'TO ' + @DatabaseUserName

PRINT @msgStatement

END

FETCH

NEXT

FROM _sysobjects

INTO

@ObjectID,

@ObjectName

END

CLOSE _sysobjects

DEALLOCATE _sysobjects

PRINT 'GO'

END






-- 2 role 권한 스크립팅 하기 

-- 아래에서 execuser 만 replace한다. 


DECLARE @DatabaseRoleName [sysname]

--SET @DatabaseRoleName = '{Database Role Name}'

SET @DatabaseRoleName = 'execuser'


SET NOCOUNT ON

DECLARE

@errStatement [varchar](8000),

@msgStatement [varchar](8000),

@DatabaseRoleID [smallint],

@IsApplicationRole [bit],

@ObjectID [int],

@ObjectName [sysname]


SELECT

@DatabaseRoleID = [uid],

@IsApplicationRole = CAST([isapprole] AS bit)

FROM [dbo].[sysusers]

WHERE

[name] = @DatabaseRoleName

AND

(

[issqlrole] = 1

OR [isapprole] = 1

)

AND [name] NOT IN

(

'public',

'INFORMATION_SCHEMA',

'db_owner',

'db_accessadmin',

'db_securityadmin',

'db_ddladmin',

'db_backupoperator',

'db_datareader',

'db_datawriter',

'db_denydatareader',

'db_denydatawriter'

)


IF @DatabaseRoleID IS NULL

BEGIN

IF @DatabaseRoleName IN 

(

'public',

'INFORMATION_SCHEMA',

'db_owner',

'db_accessadmin',

'db_securityadmin',

'db_ddladmin',

'db_backupoperator',

'db_datareader',

'db_datawriter',

'db_denydatareader',

'db_denydatawriter'

)

SET @errStatement = 'Role ' + @DatabaseRoleName + ' is a fixed database role and cannot be scripted.'

ELSE

SET @errStatement = 'Role ' + @DatabaseRoleName + ' does not exist in ' + DB_NAME() + '.' + CHAR(13) +

'Please provide the name of a current role in ' + DB_NAME() + ' you wish to script.'


RAISERROR(@errStatement, 16, 1)

END

ELSE

BEGIN

SET @msgStatement = '--Security creation script for role ' + @DatabaseRoleName + CHAR(13) +

'--Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +

'--Created By: ' + SUSER_NAME() + CHAR(13) +

'--Add Role To Database' + CHAR(13)

IF @IsApplicationRole = 1

SET @msgStatement = @msgStatement + 'EXEC sp_addapprole' + CHAR(13) +

CHAR(9) + '@rolename = ''' + @DatabaseRoleName + '''' + CHAR(13) +

CHAR(9) + '@password = ''{Please provide the password here}''' + CHAR(13)

ELSE

BEGIN

SET @msgStatement = @msgStatement + 'EXEC sp_addrole' + CHAR(13) +

CHAR(9) + '@rolename ''' + @DatabaseRoleName + '''' + CHAR(13)

PRINT 'GO'

END

SET @msgStatement = @msgStatement + '--Set Object Specific Permissions For Role'

PRINT @msgStatement

DECLARE _sysobjects

CURSOR

LOCAL

FORWARD_ONLY

READ_ONLY

FOR

SELECT

DISTINCT([sysobjects].[id]),

'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'

FROM [dbo].[sysprotects]

INNER JOIN [dbo].[sysobjects]

ON [sysprotects].[id] = [sysobjects].[id]

WHERE [sysprotects].[uid] = @DatabaseRoleID

OPEN _sysobjects

FETCH

NEXT

FROM _sysobjects

INTO

@ObjectID,

@ObjectName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msgStatement = ''

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'SELECT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'INSERT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'UPDATE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'DELETE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'EXECUTE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 26 AND [protecttype] = 205)

SET @msgStatement = @msgStatement + 'REFERENCES,'

IF LEN(@msgStatement) > 0

BEGIN

IF RIGHT(@msgStatement, 1) = ','

SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)

SET @msgStatement = 'GRANT' + CHAR(13) +

CHAR(9) + @msgStatement + CHAR(13) +

CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

CHAR(9) + 'TO ' + @DatabaseRoleName

PRINT @msgStatement

END

SET @msgStatement = ''

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 193 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'SELECT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 195 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'INSERT,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 197 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'UPDATE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 196 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'DELETE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 224 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'EXECUTE,'

IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseRoleID AND [action] = 26 AND [protecttype] = 206)

SET @msgStatement = @msgStatement + 'REFERENCES,'

IF LEN(@msgStatement) > 0

BEGIN

IF RIGHT(@msgStatement, 1) = ','

SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)

SET @msgStatement = 'DENY' + CHAR(13) +

CHAR(9) + @msgStatement + CHAR(13) +

CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +

CHAR(9) + 'TO ' + @DatabaseRoleName

PRINT @msgStatement

END

FETCH

NEXT

FROM _sysobjects

INTO

@ObjectID,

@ObjectName

END

CLOSE _sysobjects

DEALLOCATE _sysobjects

PRINT 'GO'

END




-- 작성자 

–Written By Bradley Morris
–In Query Analyzer be sure to go to
–Query -> Current Connection Options -> Advanced (Tab)
–and set Maximum characters per column
–to a high number, such as 10000, so
–that all the code will be displayed.


-- DB 전체에 부여된 권한 (user 나 role)

-- ex) grnat execute to usera

 

DECLARE @vDBUserName sysname = 'execuser'
 , @vGranteePPID int  = null
 , @vLoopCnt  int  = 1
 , @vRowCnt  int  = 0
 , @vGrantScript nvarchar(max)

SELECT @vGranteePPID=uid
  FROM sys.sysusers
 WHERE name = @vDBUserName


IF OBJECT_ID('tempdb..#TBLX') IS NOT NULL
 EXEC (N'DROP TABLE #TBLX')
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS IDX
 , CAST(state_desc AS nvarchar(max)) + N' ' + permission_name + N' TO ' + N'[' + @vDBUserName + N']' AS SCRIPT
 --, state_desc
 --, permission_name
 --, @vDBUserName
  INTO #TBLX
  FROM sys.database_permissions
 WHERE grantee_principal_id=@vGranteePPID

SET @vRowCnt=@@ROWCOUNT

--GRANT CONNECT TO [SVC_WEB_PUSHSSVR_ACC]
--GRANT EXECUTE TO [SVC_WEB_PUSHSSVR_ACC]

WHILE 1=1
BEGIN
 
 SELECT @vGrantScript=SCRIPT
   FROM #TBLX
  WHERE IDX = @vLoopCnt

 PRINT @vGrantScript

 IF @vRowCnt=@vLoopCnt or @vRowCnt = 0
  BREAK;
 ELSE
  SET @vLoopCnt+=1
END

 

-- written by 차태욱


 

http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database


/*

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.                 

*/


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

저작자 표시 비영리 변경 금지
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

티스토리 툴바