[MSSQL] SQL SERVER 2005 사용자 추가 및 계정 권한 컨트롤

데이터 모델을 만들어 DB를 관리하는 경우 이런 고민이 있을 것이다.

운영 SQL Server 에 개발자들이 무분별하게 접속하여 작업을 하게 되면 DB모델에는 반영되지 않은 사항이 물리DB에는 반영되어, 모델 관리가 어렵게 될 수 있다. 그리고 시간이 지나면 동기화가 되지 않아 모델 자체를 신뢰할 수 없게 되는 상황까지 갈 것이다. 아니면 다시 리버스 하여 모델을 다시 그리던지..

이런 문제를 애초에 잡고자, 개발DB는 개발자가 마음데로 작업할 수 있지만, 운영DB에서는 조회만 가능하게 권한을 막아보자

그럴려면, 각 개발자별로 아이디를 부여하고, 해당 아이디가 사용하는 DB들을 정의 한 후에 dbo 스키마를 기본으로 적용해주고, 해당 아이디의 권한을 명시적으로 부여하면 된다. (처음에는 Role을 사용하여 쉽게 하려 했으나 내공이 딸려서 포기! ㅠㅠ 아흑)

아래 스크립트에 대한 사전 조건은 이렇다.

사용자 ID = 2000000

사용자 PW = p20000000

기본DB명 = DB_NAME

기본schema = dbo

해당 사용자가 가지게 될 권한 =

SELECT / EXECUTE / REFERENCES / SHOWPLAN / SUBSCRIBE QUERY NOTIFICATIONS / VIEW DATABASE STATE / VIEW DEFINITION

--// 로그인계정생성, 해당로그인계정의기본DB설정 USE [master] GO CREATE LOGIN [20000000] WITH PASSWORD=N'p20000000', DEFAULT_DATABASE=[DB_NAME], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --// 사용할DB에사용자생성 USE [DB_NAME] GO CREATE USER [20000000] FOR LOGIN [20000000] GO --// 해당사용자의기본스키마지정 USE [DB_NAME] GO ALTER USER [20000000] WITH DEFAULT_SCHEMA=[dbo] GO --// 해당DB 내에서의사용권한설정 --// EXECUTE ,SELECT, SHOWPLAN, VIEW 등 use [DB_NAME] GO GRANT EXECUTE TO [20000000] GO use [DB_NAME] GO GRANT REFERENCES TO [20000000] GO use [DB_NAME] GO GRANT SELECT TO [20000000] GO use [DB_NAME] GO GRANT SHOWPLAN TO [20000000] GO use [DB_NAME] GO GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [20000000] GO use [DB_NAME] GO GRANT VIEW DATABASE STATE TO [20000000] GO use [DB_NAME] GO GRANT VIEW DEFINITION TO [20000000] GO