[MSSQL] 프로시저 생성 권한 부여하기

MSSQL에서 프로시저에 대해 사용자에게 권한을 부여할 때 아래의 스크립트만 적용할 경우 오류가 발생하게 됩니다.

GRANT CREATE PROCEDURE TO [UserName];

 

 

 

왜 생성이 불가할까?

• CREATE 권한은 객체를 생성 할 수 있는 기능만을 제공
• 새로 생성되는 객체를 배치할 수 있는 공간이 필요한데 이 공간이 바로 스키마라는 컨테이너 

 

따라서 CREATE 권한을 부여하기 위해서는 스키마를 변경할 수 있는 ALTER 권한이 필요합니다.

--프로시저를 생성할 수 있는 권한 부여
GRANT CREATE PROCEDURE TO [UserName];

--스키마를 변경할 수 있는 권한 부여
GRANT ALTER ON SCHEMA::[dbo] TO [UserName];

 

 

데이터베이스 수준 역할 부여

db_ddladmin 는 데이터베이스에서 모든 DDL(데이터 정의언어) 명령을 실행할 수 있음으로 해당 권한을 부여해주는 방법도 있습니다.

EXEC sp_addrolemember db_ddladmin, [UserName]

다만 db_ddladmin 권한의 경우 프로시저 생성 이외  다른 객체에 대한 생성 및 수정도 가능합니다.

 

 

특정 객체에 대해서만 생성 권한 부여하기

• 프로시저를 관리할 스키마를 생성
• 해당 스키마에 ALTER 권한을 부여

 

이처럼 특정 객체에 대해서 권한 부여가 필요할 경우 스키마를 생성하여 관리하면 됩니다.

--스키마 생성
CREATE SCHEMA Chains;
GO

--프로시저를 생성할 수 있는 권한 부여
GRANT CREATE PROCEDURE TO [UserName];

--스키마를 변경할 수 있는 권한 부여
GRANT ALTER ON SCHEMA::[Chains] TO [UserName];

--스키마 조회
SELECT * FROM SYS.schemas;