[MS SQL] 사용자 계정과 관련된 오브젝트 확인 및 삭제

SQL Server 2005에서는 소유자가 스키마를 소유하고 스키마가 오브젝트를 소유합니다.

> 기호의 벌어진 쪽이 좁혀진 쪽을 소유한다는 의미로 사용했을 때, 다음과 같이 표현할 수 있습니다.

소유자 > 스키마 > 오브젝트

 

따라서 소유자를 삭제할 경우에는 삭제할 소유자가 소유한 스키마의 소유권을 새로운 소유자에게 이전한 후 기존 소유자를 삭제하면 됩니다.

다음의 쿼리를 한 줄씩 실행해보시면 소유자 확인 및 이전, 삭제의 방법을 알게 되시리라 생각합니다.

반드시 충분한 테스트를 해보시고 실전에 사용하시기 바랍니다. ^^;;;

 

 

- SQL Server 2005의 경우

 

00	/*************************************************************/
01	-- 사용자와 관련 오브젝트 목록 확인 및 사용자 삭제 - SQL Server 2005
02	/*************************************************************/
03	USE master;
04	GO
05
06	/*************************************************************/
07	/**-- 테스트를 위한 사용자, 스키마, 테이블 생성--------------------------*/
08
09	-- 테스트 데이터 베이스 생성
10	CREATE DATABASE AccTestDB;
11
12	-- 로그인 생성
13	CREATE LOGIN AccountTestA WITH PASSWORD = 'test1';
14	CREATE LOGIN AccountTestB WITH PASSWORD = 'test2';
15	GO
16
17	-- 사용자 생성
18	USE AccTestDB;
19	GO
20	CREATE USER AccountTestA FOR LOGIN AccountTestA WITH DEFAULT_SCHEMA = AccSchema;
21	CREATE USER AccountTestB FOR LOGIN AccountTestB WITH DEFAULT_SCHEMA = AccSchema;
22	GO
23
24	-- 테이블 생성 권한을 위한 역할 추가
25	EXEC sp_addrolemember 'db_owner', 'AccountTestA';
26	EXEC sp_addrolemember 'db_owner', 'AccountTestB';
27	GO
28
29	-- 스키마 생성
30	CREATE SCHEMA AccSchema AUTHORIZATION AccountTestA;
31
32	-- 사용자 생성 확인
33	SELECT * FROM sys.sysusers;
34	SELECT * FROM sys.schemas;
35
36	-- SQL Server 2005에서는 소유자.테이블이 아닌 스키마.테이블로 테이블을 생성
37	EXECUTE AS LOGIN = 'AccountTestA';
38	GO
39	CREATE TABLE AccSchema.AccTable (Col1 int);
40	GO
41
42	/*************************************************************/
43	/**-- AccountTestA가 소유자인 오브젝트 목록 조회-------------------------*/
44	SELECT u.name as UserName, s.name as SchemaName, o.Type as ObjectType, o.name as ObjectName
45	FROM sys.sysusers u
46	JOIN sys.schemas s ON u.uid = s.principal_id
47	JOIN sys.objects o ON s.schema_id=o.schema_id
48	WHERE u.name = 'AccountTestA'
49
50	/*************************************************************/
51	/**-- SQL Server 2005에서는 소유자가 스키마를 소유하고
52	스키마가 오브젝트를 소유하기 때문에
53	소유자를 삭제할 경우에는 오브젝트를 삭제하지 않고
54	삭제할 소유자가 소유한 스키마의 소유권을 이전한 후,
55	소유자를 삭제한다. --*/
56
57	-- 소유자삭제
58	DROP USER AccountTestA
59	/*
60	메시지15138, 수준16, 상태1, 줄1
61	데이터베이스 보안 주체는 데이터베이스의 스키마을(를) 소유하며 삭제할 수 없습니다.
62	*/
63
64	-- AccountTestA 소유자인 테이블 목록 조회
65	SELECT u.name as UserName, s.name as SchemaName
66	FROM sys.sysusers u JOIN sys.schemas s ON u.uid = s.principal_id
67	WHERE u.name = 'AccountTestA'
68
69	-- 소유권 이전
70	ALTER AUTHORIZATION ON SCHEMA::AccSchema TO AccountTestB;
71	GO
72
73	-- 삭제하려는 소유자를 가진 테이블 삭제 후, 사용자 삭제
74	DROP USER AccountTestA;
75	GO
76	SELECT * FROM sys.sysusers WHERE name = 'AccountTestA';
77	GO
78	/*
79	메시지916, 수준14, 상태1, 줄1
80	현재 보안 컨텍스트로는 서버 보안 주체 "AccountTestA"이(가) 데이터베이스 "AccTestDB"에 액세스 할 수 없습니다.
81
82	*/
83
84	/*************************************************************/
85	-- 데이터베이스 삭제
86	REVERT;
87
88	USE master;
89	DROP DATABASE AccTestDB;
90
91	-- 로그인 삭제
92	DROP LOGIN AccountTestA;
93	DROP LOGIN AccountTestB;

 

 

 

- SQL Server 2000의 경우

 

00	/*************************************************************/
01	-- 사용자와 관련 오브젝트 목록 확인- SQL Server 2000
02	/*************************************************************/
03	USE master;
04	GO
05
06	/*************************************************************/
07	/**-- 테스트를 위한 사용자 및 테이블 생성----------------------------------------------*/
08
09	-- 테스트 데이터베이스 생성
10	CREATE DATABASE AccTestDB
11	GO
12
13	-- 로그인 생성
14	EXEC sp_addlogin 'AccountTest', 'test', 'AccTestDB'
15
16	-- 사용자 생성
17	USE AccTestDB
18	GO
19	EXEC sp_adduser 'AccountTest'
20	GO
21
22	-- 테이블 생성 권한을 위한 역할 추가
23	EXEC sp_addrolemember 'db_owner', 'AccountTest'
24
25	-- 사용자 생성 확인
26	SELECT * FROM sysusers WHERE name = 'AccountTest'
27
28	-- 실행 사용자를 AccountTest로 변경
29	SETUSER 'AccountTest'
30
31	-- 관련 테이블 생성
32	CREATE TABLE AccTable (Col1 int)
33
34
35	/*************************************************************/
36	/**-- AccountTest가 소유자인 오브젝트 목록 조회-------------------------------------*/
37	SELECT o.xType as ObjectType, u.name as UserName, o.name as ObjectName
38	FROM sysusers u JOIN sysobjects o ON u.uid=o.uid
39	WHERE u.name = 'AccountTest'
40
41
42	/*************************************************************/
43	/**-- 소유자 삭제 테스트-------------------------------------------------------------------*/
44	-- 소유자 삭제
45	EXEC sp_dropuser 'AccountTest'
46	/*
47	서버: 메시지15183, 수준16, 상태1, 프로시저sp_MScheck_uid_owns_anything, 줄17
48	데이터베이스의 개체를 소유하고 있으므로 사용자를 삭제할 수 없습니다.
49	*/
50
51	-- AccountTest가 소유자인 테이블 목록 조회
52	SELECT u.name, o.name FROM sysusers u JOIN sysobjects o ON u.uid=o.uid
53	WHERE u.name = 'AccountTest' and o.xType='U'
54
55	-- 새 로그인 생성
56	SETUSER
57	EXEC sp_addlogin 'AccountTest2';
58
59	-- 새 사용자 생성
60	EXEC sp_adduser 'AccountTest2';
61
62	-- 소유자 이전
63	EXEC sp_changeobjectowner 'AccountTest.AccTable' , 'AccountTest2';
64	/*
65	주의: 개체 이름부분을 변경하면 스크립트나 저장프로시저를 손상시킬 수 있습니다.
66	*/
67
68	-- 소유자 삭제 재시도
69	EXEC sp_dropuser 'AccountTest'
70
71	-- 로그인 삭제
72	EXEC sp_droplogin 'AccountTest'
73
74	-- 확인
75	SELECT * FROM master.dbo.SYSXLOGINS
76	SELECT * FROM SYSUSERS
77
78	/**--테스트 완료------------------------------------------------------------------------*/
79	-- 데이터베이스 삭제
80	USE master
81	DROP DATABASE AccTestDB
82
83	-- 로그인 삭제
84	EXEC sp_droplogin 'AccountTest2'
85
86	-- 확인
87	SELECT * FROM master.dbo.SYSXLOGINS
88	SELECT * FROM SYSUSERS

 

[출처] [MS SQL] 사용자 계정과 관련된 오브젝트 확인 및 삭제 (서버관리 마스터 - OS, WAS, DB, Network)