MS-SQL DB 제어

MS-SQL DB에서 DB 생성, 삭제 및 사용자 추가 및 제거, DB 권한 제어를 하는 방법입니다. 아래 방법이 절대적이지 않고 가장 좋은 방법이 아닙니다. 아래는 제가 급하게 수집하여 정리한 내용이라서 조금씩 완성해 나갈려고 합니다. 물론 필요할때만 합니다. ^^;
아래 내용이 틀리거나 혹은 더 좋은 방법이 있다면 코멘트를 주시면 적극 반영하도록 하겠습니다.

DB 관리

DB 목록 조회

SELECT name FROM sys.databases
SELECT name FROM sys.sysdatabases
EXEC sp_helpdb

DB 생성

CREATE DATABASE DB명
ex) CREATE DATABASE TEST_DB
CREATE DATABASE DB명 ON ( NAME=db_name, FILENAME='DB 파일명', SIZE=초기크기, MAXSIZE=최대크기, FILEGROWTH=증가크기)

DB 제거

DROP DATABASE DB명
ex) DROP DATABASE TEST_DB

DB 변경

USE DB명
ex) USE TEST_DB

테이블 관리

테이블 생성

CREATE TABLE [DB명.]테이블명 ( 컬럼명 데이터형 [NOT NULL])
ex)
CREATE TABLE test_record ( name nvarchar(MAX) NOT NULL )
CREATE TABLE TEST_DB.test_record( name nvarchar(128) NOT NULL, phone nvarchar(12) )

사용자관리

인증 사용자 추가

EXEC sp_addlogin '사용자ID', '암호', 'DB명'
ex) EXEC sp_addlogin 'user1', 'password1', 'TEST_DB'

인증 사용자 제거

EXEC sp_droplogin '사용자ID'
ex) EXEC sp_droplogin 'user1'

DB 사용자 추가

현재 데이터베이스에 새로운 사용자에 추가

EXEC sp_adduser '사용자ID'[, 'DB에서 사용자명'[, '그룹명']]
ex) EXEC sp_adduser 'user1'

DB 사용자 제거

현재 데이터베이스에서 데이터베이스 사용자를 제거. 이전 버전 호환성을 위해 제공됨

EXEC sp_dropuser '사용자ID'
ex) EXEC sp_dropuser 'user1'

권한관리

DB 사용 권한 부여

DB 객체 및 쿼리에 대한 사용자 권한을 설정

GRANT select, update, delete, intert ON TEST_DB.dbo.test_record TO user1
ex) GRANT select(test_col1) ON test_record TO user1

만약 사용자가 할당받은 권한 내에서 다른 사용자에게도 권한을 부여할 수 있는 방법

GRANT select ON test_table TO user1 WITH GRANT OPTION

DB 사용 권한 조회

EXEC sp_helprotect
ex)
EXEC sp_helpprotect NULL, NULL, dbo
EXEC sp_helprotect @grantorname = 'dbo'

• 테이블 사용권한나열: EXEC sp_helprotect '테이블명'
• 사용자 사용권한나열: EXEC sp_helprotect NULL, '사용자ID'
• 특정 사용자에 의해 부여된 사용권한 나열: EXEC sp_helprotect NULL, NULL, '사용자ID'
• 쿼리 사용권한 나열: EXEC sp_helprotect NULL, NULL, NULL, 's'

DB 사용 권한 제거

REVOKE select, update, delete, insert ON test_table FROM user1

만약 권한이 제거된 사용자가 다른 사용자에게도 권한을 부여할 수 있었다면, 다른 사용자에 부여된 권한도 같이 제거

REVOKE delete ON test_table FROM user1 CASCADE

참조

[1] siiwoo, MS-SQL 계정추가쿼리, http://siiwoo.egloos.com/1123095