보미네

http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists

거짓말하는 많은 사람들이 사라졌으면 좋겠다.

를 읽어보고 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. 이제 원하는 콜스택정보를 확인할 수 있다.

드디어 출간 되었습니다.

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

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] 

robocopy i:\ d:\t /MIR /R:1 /ZB /SEC /W:1

D:\>takeown /f backup /r /d y

Display types:*
  

회사의 mac 등록이라는 절차 정말 비 효율적이다.

먼저 자산을 다 넣어놓고 DB화 하고 지급받은 자산을 사원이 사내시스템을 통해 사번, 시리얼, mac을 넣으면 자동으로

등록이 되어야지 전화하고 메일쓰고 정말 힘들다.......

이제 그냥 공유기 만들어 쓸란다.

관리자 모드로 cmd 시작해서

1. 와이파이 무선 네트워크가 호스트된 네트워그 지원 여부 확인

netsh wlan show drivers

2. 사용할 식별번호 ssid 와 접속시 필요한 암호 설정 여기서는 12345678

netsh wlan set hostednetwork mode=allow ssid=kimms key=12345678

3. 호스트된 네트워크 시작

netsh wlan start hostednetwork

4. 잘 붙나 확인하는 명령어

netsh wlan show hostednetwork

5. 유선을 이용해 무선 ap 를 만드는 것이니 유선랜에 인터넷 공유 설정을 해줘야 한다. kimms 에게

* 핸드폰이나 스마트 기기가 잘 안 붙으면 휴대 기기의 wifi 를 on/off 한 후 사용

잘 된다.~~~

netsh wlan show drivers
netsh wlan set hostednetwork mode=allow ssid=kimms key=12345678
netsh wlan start hostednetwork
netsh wlan show hostednetwork

이 시나리오는 기업에서 매번 똑같은 세팅으로 컴퓨터를 셋팅할때 아주 유용하다.

수십개의 프로그램과 다양한 셋팅을 해야하는 여러 PM을 설치할 일이 생겼다. 

아래 절차는 ghost 등 상용프로그램 없이 깨끗하게 vm의 native booting 기능을 이용해 해결했다. 

성능은 원래의 PM과 동일하나 bitlocker 등의 기능은 쓸 수 없다. 

그러나, os가 맛이가면 복사한 다른 vhdx로 부팅도 가능하다. 획기적이지 않은가? (요건 다른 드라이브에...)

누나 형 언니 삼촌 이모 동생이 아직도 컴터 다시깔아 달라고 하지 않는가? 

쿨럭~ 추석에 USB 하나 들고가서 해주고 오자 

해주고 올때 teamviewer 개인용도 하나 깔아주고 오면 들어가서 셋팅해주면 신기해 하실꺼얌.~

1

다음 동영상을 보고 windows PE 를 만든다.

http://www.youtube.com/watch?v=UJWGeK7_XDE

HDD 에 만들기 위해 아래 아티클을 참고한다.

http://technet.microsoft.com/en-us/library/hh825109.aspx

Storing windows images on the windows pe drive 를 참고한다.

2

USB 데이터 파티션이나 다른 USB 에 vhdx 를 copy 한다.

3

USB 에서 PM의 HDD를 포멧하고 vhdx 를 복사한다.

http://technet.microsoft.com/en-us/library/hh825691.aspx

Clean and partition the destination computer 를 참고한다.

4

같은 문서에서 bcdboot를 이용해 booting 기능을 추가한다.

Step 4를 참고한다. 

http://technet.microsoft.com/en-us/library/hh825709.aspx

1에서 

windows 8.1 adk download

adksetup 실행

deployment and imaging tools environment 를 찾아 실행 (win + q)

copype amd64 e:\winpe_amd64

MakeWinPEMedia /UFD e:\winpe_amd64 p:

여기서 p: 가 USB 플래시 드라이브이거나 USB HDD에서 fat32로 만든 파티션이다. 

이 과정이 끝나면 부팅 가능한 windowspe 가 완성된다. 

2에서 

USB HDD에 hyper-v로 정성들여 만들어놓은 vhdx를 넣는다.

3에서 

windowspe 로 부팅한 후 PM의 하드드라이브를 포멧 및 파티션하는 과정을 거친다. 

diskpart

select disk 0

clean

create partition primary size=300

format quick fs=ntfs

assign letters=s

active

create partition primary

format quick fs=ntfs

assign letter=c

exit

copy windows81.vhdx c:\

4에서 

diskpart

select vdisk file=c:\windows81.vhdx

attach vdisk

list volume

select volume 숫자볼륨번호

assign letter=v

exit

v:

cd v:\windows\system32

bcdboot v:\Windows

USB HDD 제거 후 리부팅

vhdx 를 이용해 컴퓨터를 장악한다. 

만약, c 드라이브에 다른 os 가 깔려있다면 

이런 방법으로 원래 컴퓨터 주인의 모든 파일을 억세스 할 수 있고 

흔적도 없이 vhdx 를 지우고 나올 수 있다. ^.^~ 메롱~ (스텝 3에서 clean 했으면 주인한테 맞아 죽는다..ㅎ~)

msconfig 나 bcdedit 를 이용해 수정하면 된다. 

이 모두가 쓸모없어지면

vdisk 를 제거한다.

diskpart

select vdisk file=v:\windows81.vhdx

detach vdisk

exit

windows 7 의 경우 sysprep 일반화를 해야지 native booting 이 가능하다.

cd windows\system32\sysprep

sysprep

일반화 체크

시스템 종료 선택

끝~

copy 해서 넣고 native booting 하면 된다.

http://cappleblog.co.kr/335

지원되는 모든 언어를 포함하는 System Update 5.06

ThinkVantage 시스템 업데이트(System Update)를 이용하면 특정 지식 없이도, 인터넷을 통해 Lenovo 서버에서 직접 Lenovo 소프트웨어, 드라이버, BIOS를 다운로드하고 업데이트할 수 있습니다.

이번 버전의 새로운 점

• 일부 이슈 수정

Note:
System Update 5.05는 Microsoft Windows 8, Windows 8.1, Windows 7만 지원합니다.

Microsoft Windows XP 및 Windows Vista를 사용하는 경우, 아래의 System Update 3.16 를 참고하십시오.

System Update의 정상 작동에는 Microsoft .NET Framework version 3.5 with Service pack 1 또는 그 이상의 버전이 필요합니다.