블로그 이미지
010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (500)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total
Today
Yesterday

달력

« » 2024.3
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.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 보미아빠
, |

최근에 달린 댓글

최근에 받은 트랙백

글 보관함