프로시져 권한부여 생성 쿼리

SELECT 'GRANT ' + (CASE xtype WHEN 'TF' THEN 'select' ELSE 'execute' END) + ' ON ' + NAME + ' TO coupon_manager'
FROM dbo.sysobjects WITH (nolock)
where xtype in ('P','X','FN','IF','TF')
and category <> 2
and name not like 'dt%'
order by NAME

서버의 권한 계층 조회 sys.fn_builtin_permissions

sys.fn_builtin_permissions

서버의 기본 제공 사용 권한 계층에 대한 설명을 반환합니다.

sys.fn_built_permissions( [ DEFAULT  NULL ]
     empty_string  '' } )
 ::=
      APPLICATION ROLE  ASSEMBLY  ASYMMETRIC KEY
     CERTIFICATE  CONTRACT  DATABASE  ENDPOINT  FULLTEXT CATALOG
     LOGIN  MESSAGE TYPE  OBJECT  REMOTE SERVICE BINDING  ROLE
     ROUTE  SCHEMA  SERVER  SERVICE  SYMMETRIC KEY  TYPE
     USER  XML SCHEMA COLLECTION

열 이름 
데이터형식 
데이터정렬 
설명 
class_desc
nvarchar(60)
서버의 데이터 정렬
보안 개체 클래스에 대한 설명입니다.
permission_name
sysname
서버의 데이터 정렬
사용 권한 이름입니다.
type
char(4)
서버의 데이터 정렬
단축 사용 권한 유형 코드입니다. 다음 표를 참조하십시오.
covering_permission_name
sysname
서버의 데이터 정렬
NULL이 아니면 이 클래스에 대한 다른 사용 권한을 포함하는 사용 권한의 이름입니다. 
parent_class_desc
nvarchar(60)
서버의 데이터 정렬
NULL이 아니면 현재 클래스를 포함하는 부모 클래스의 이름입니다.
parent_covering_permission_name
sysname
서버의 데이터 정렬
NULL이 아니면 부모 클래스에 대한 다른 모든 사용 권한을 포함하는 사용 권한의 이름입니다.

WITH GRANT OPTION 으로 연결된 권한 철회

admin connect -- 관리자로 접속

create login test with password='mssql'

create user test for login test

grant alter on test to test -- 권한상속권한을 같이 부여
with grant option

grant select on test to test
with grant option

create login test2 with password='mssql'

create user test2 for login test2

user test connect

grant alter on test2 to test2 -- 위에서 권한상속권한을 부여받았으므로 권한부여가능

grant select on test2 to test2

select * from test

alter table test add d int

revoke alter on test from test -- 권한을 뺏으려고 하면 하위 계정의 권한때문에 뺏지못함

revoke alter on test from test cascade --모든 하위계정 권한 뺏기 cascade

권한조회 SQL

select name, owner = user_name(uid), crdate, objtype = sysstat & 0xf, id, deltrig from dbo.sysobjects o where power(2, sysstat & 0xf) & 31 != 0 and not (OBJECTPROPERTY(id, N'IsDefaultCnst') = 1 and category & 0x0800 != 0) and o.name not like N'#%' order by name, owner

select a = o.name, b = user_name(o.uid), user_name(p.uid), o.sysstat & 0xf, p.id, action, protecttype from dbo.sysprotects p, dbo.sysobjects o, master.dbo.spt_values a where o.id = p.id and (( p.action in (193, 197) and ((p.columns & 1) = 1) ) or ( p.action in (195, 196, 224, 26) )) and (convert(tinyint, substring( isnull(p.columns, 0x01), a.low, 1)) & a.high != 0) and a.type = N'P' and a.number = 0 and p.uid = 0 order by a, b

user_name 함수는 uid를 받아서 user_name으로 표시해준다.

select * from sys.database_role_members as a join sys.sysusers as b ona.role_principal_id = b.uid or a.member_principal_id = b.uid

현재 데이터베이스 유저들의 권한조회