work smart !!

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;

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