테이블 소유자 확인 및 변경

테이블 소유자 확인 및 변경   - 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   작성자 : troy , 등록일 : 2008-08-04 오후 6:20:00 SQL 에서 필요없는 Account 를 삭제하려 하는데 해당 계정이 어떤 개체에 ownership을 가지고 있거나 하면 삭제가 되지 않습니다. 문제는 그 많은 objects 중에 어떤 넘이 그러한 ownership에 해당되는지 찾기가 복잡하다는 것이지요 ? 혹여 sp_depents 와 같이 account 에도 적용할 수 있는 SP 나 Script를 알고 계신 분이 있다면 도움 부탁드립니다.