소유자 관련

간혹 발생하는 소유관련 문제...
관련 정보 중 가장 도움이 되었던 예제이다.

http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=2368

테이블 소유자 확인 및 변경
 
- SQL Server 2005의 경우
 
SQL Server 2005에서는 소유자가 스키마를 소유하고 스키마가 오브젝트를 소유합니다.
> 기호의 벌어진 쪽이 좁혀진 쪽을 소유한다는 의미로 사용했을 때, 다음과 같이 표현할 수 있습니다.
소유자 > 스키마 > 오브젝트
 
따라서 소유자를 삭제할 경우에는 삭제할 소유자가 소유한 스키마의 소유권을 새로운 소유자에게 이전한 후 기존 소유자를 삭제하면 됩니다.
다음의 쿼리를 한 줄씩 실행해보시면 소유자 확인 및 이전, 삭제의 방법을 알게 되시리라 생각합니다.
반드시 충분한 테스트를 해보시고 실전에 사용하시기 바랍니다. ^^;;;
 
/*************************************************************/
-- 사용자와 관련 오브젝트 목록 확인 및 사용자 삭제 - SQL Server 2005
/*************************************************************/
USE master;
GO
 
/*************************************************************/
/**-- 테스트를 위한 사용자, 스키마, 테이블 생성----------------*/
 
-- 테스트 데이터 베이스 생성
CREATE DATABASE AccTestDB;
 
-- 로그인 생성
CREATE LOGIN AccountTestA WITH PASSWORD = 'test1';
CREATE LOGIN AccountTestB WITH PASSWORD = 'test2';
GO
 
-- 사용자 생성
USE AccTestDB;
GO
CREATE USER AccountTestA FOR LOGIN AccountTestA WITH DEFAULT_SCHEMA = AccSchema;
CREATE USER AccountTestB FOR LOGIN AccountTestB WITH DEFAULT_SCHEMA = AccSchema;
GO
 
-- 테이블 생성 권한을 위한 역할 추가
EXEC sp_addrolemember 'db_owner', 'AccountTestA';
EXEC sp_addrolemember 'db_owner', 'AccountTestB';
GO
 
-- 스키마 생성
CREATE SCHEMA AccSchema AUTHORIZATION AccountTestA;
 
-- 사용자 생성 확인
SELECT * FROM sys.sysusers;
SELECT * FROM sys.schemas;
 
-- SQL Server 2005에서는 소유자.테이블이 아닌 스키마.테이블로 테이블을 생성
EXECUTE AS LOGIN = 'AccountTestA';
GO
CREATE TABLE AccSchema.AccTable (Col1 int);
GO
 
/*************************************************************/
/**-- AccountTestA가 소유자인 오브젝트 목록 조회-------------*/
SELECT u.name as UserName, s.name as SchemaName, o.Type as ObjectType, o.name as ObjectName
FROM sys.sysusers u
                           JOIN sys.schemas s ON u.uid = s.principal_id
                           JOIN sys.objects o ON s.schema_id=o.schema_id
WHERE u.name = 'AccountTestA'
 
/*************************************************************/
/**-- SQL Server 2005에서는 소유자가 스키마를 소유하고
                           스키마가 오브젝트를 소유하기 때문에
        소유자를 삭제할 경우에는 오브젝트를 삭제하지 않고
                           삭제할 소유자가 소유한 스키마의 소유권을 이전한 후,
                           소유자를 삭제한다. --*/
 
-- 소유자삭제
DROP USER AccountTestA
/*
메시지15138, 수준16, 상태1, 줄1
데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다.
*/
 
-- AccountTestA 소유자인 테이블 목록 조회
SELECT u.name as UserName, s.name as SchemaName
FROM sys.sysusers u JOIN sys.schemas s ON u.uid = s.principal_id
WHERE u.name = 'AccountTestA'
 
-- 소유권 이전
ALTER AUTHORIZATION ON SCHEMA::AccSchema TO AccountTestB;
GO
 
-- 삭제하려는 소유자를 가진 테이블 삭제 후, 사용자 삭제
DROP USER AccountTestA;
GO
SELECT * FROM sys.sysusers WHERE name = 'AccountTestA';
GO
/*
메시지916, 수준14, 상태1, 줄1
현재 보안 컨텍스트로는 서버 보안 주체 "AccountTestA"이(가) 데이터베이스 "AccTestDB"에 액세스 할 수 없습니다.
 
*/
 
/*************************************************************/
-- 데이터베이스 삭제
REVERT;
 
USE master;
DROP DATABASE AccTestDB;
 
-- 로그인 삭제
DROP LOGIN AccountTestA;
DROP LOGIN AccountTestB;
 
 
 
- SQL Server 2000의 경우
 
/*************************************************************/
-- 사용자와 관련 오브젝트 목록 확인- SQL Server 2000
/*************************************************************/
USE master;
GO
 
/*************************************************************/
/**-- 테스트를 위한 사용자 및 테이블 생성----------------------*/
 
-- 테스트 데이터베이스 생성
CREATE DATABASE AccTestDB
GO
 
-- 로그인 생성
EXEC sp_addlogin 'AccountTest', 'test', 'AccTestDB'
 
-- 사용자 생성
USE AccTestDB
GO
EXEC sp_adduser 'AccountTest'
GO
 
-- 테이블 생성 권한을 위한 역할 추가
EXEC sp_addrolemember 'db_owner', 'AccountTest'
 
-- 사용자 생성 확인
SELECT * FROM sysusers WHERE name = 'AccountTest'
 
-- 실행 사용자를 AccountTest로 변경
SETUSER 'AccountTest'
 
-- 관련 테이블 생성
CREATE TABLE AccTable (Col1 int)
 
 
/*************************************************************/
/**-- AccountTest가 소유자인 오브젝트 목록 조회---------------*/
SELECT o.xType as ObjectType, u.name as UserName, o.name as ObjectName
FROM sysusers u JOIN sysobjects o ON u.uid=o.uid
WHERE u.name = 'AccountTest'
 
 
/*************************************************************/
/**-- 소유자 삭제 테스트-------------------------------------*/
-- 소유자 삭제
EXEC sp_dropuser 'AccountTest'
/*
서버: 메시지15183, 수준16, 상태1, 프로시저sp_MScheck_uid_owns_anything, 줄17
데이터베이스의 개체를 소유하고 있으므로 사용자를 삭제할 수 없습니다.
*/
 
-- AccountTest가 소유자인 테이블 목록 조회
SELECT u.name, o.name FROM sysusers u JOIN sysobjects o ON u.uid=o.uid
WHERE u.name = 'AccountTest' and o.xType='U'
 
-- 새 로그인 생성
SETUSER
EXEC sp_addlogin 'AccountTest2';
 
-- 새 사용자 생성
EXEC sp_adduser 'AccountTest2';
 
-- 소유자 이전
EXEC sp_changeobjectowner 'AccountTest.AccTable' , 'AccountTest2';
/*
주의: 개체 이름부분을 변경하면 스크립트나 저장프로시저를 손상시킬 수 있습니다.
*/
 
-- 소유자 삭제 재시도
EXEC sp_dropuser 'AccountTest'
 
-- 로그인 삭제
EXEC sp_droplogin 'AccountTest'
 
-- 확인
SELECT * FROM master.dbo.SYSXLOGINS
SELECT * FROM SYSUSERS
 
/**--테스트 완료------------------------------------------------------------------------*/
-- 데이터베이스 삭제
USE master
DROP DATABASE AccTestDB
 
-- 로그인 삭제
EXEC sp_droplogin 'AccountTest2'
 
-- 확인
SELECT * FROM master.dbo.SYSXLOGINS
SELECT * FROM SYSUSERS