1 로그인

2 스키마

3 사용자 및 권한부여

4 테스트

5 참고

--로그인 생성
USE master;

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'yasicom')
	DROP LOGIN yasicom;

CREATE LOGIN yasicom
WITH 
	PASSWORD = 'pw1234!@#$' 
,	DEFAULT_DATABASE = tempdb
,	CHECK_EXPIRATION = OFF
,	CHECK_POLICY = OFF;
GO	

--스키마 생성
USE tempdb;

IF EXISTS(SELECT * FROM sys.schemas WHERE name = 'TestSchema')
	DROP SCHEMA PubDiv;
GO

CREATE SCHEMA TestSchema
GO

--alter schema dbo transfer ABC.TableAAA

--사용자 생성
IF EXISTS(select * from sys.database_principals WHERE name = 'yasicom')
	DROP USER yasicom;
GO

CREATE USER yasicom FOR LOGIN yasicom
WITH DEFAULT_SCHEMA = TestSchema;
GO

--권한부여
GRANT SELECT ON SCHEMA::TestSchema TO yasicom;
GO

/*
DB소유자인 경우
EXEC sp_addrolemember 'db_owner', '사용자명'
GO
*/

--테스트
CREATE TABLE TestSchema.Test(nm varchar(20));
INSERT TestSchema.Test VALUES('Hello World!');

--사용자변경:yasicom
EXECUTE AS USER = 'yasicom';
SELECT USER_NAME(); --yasicom

SELECT * FROM TestSchema.Test

--사용자변경:dbo
REVERT
SELECT USER_NAME(); --dbo

ALTER AUTHORIZATION ON Database::XXXX TO sa;