SQL Server正确删除Windows认证用户的方法

1

2

3

4

5

USE [master]

GO

DROP LOGIN [xxx\xxxx]

GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

DECLARE @login_name sysname;

SET @login_name='GFG1\chenzhenh'

SELECT d.name AS database_name,

owner_sid   AS owner_sid ,

l.name AS database_owner

FROM sys.databases d

LEFT JOIN sys.syslogins l ON l.sid = d.owner_sid

WHERE ***=@login_name;

SELECT 'USE ' + *** + CHAR(10)

+ 'GO' + CHAR(10)

+ 'EXEC   dbo.sp_changedbowner @loginame =N''sa'', @map = false' AS change_db_owner_cmd

FROM sys.databases d

LEFT JOIN sys.syslogins l ON l.sid = d.owner_sid

WHERE *** = @login_name;

SELECT j.job_id AS JOB_ID

,*** AS JOB_NAME

,CASE WHEN [enabled] =1 THEN 'Enabled'

ELSE 'Disabled' END AS JOB_ENABLED

,*** AS JOB_OWNER

,j.category_id   AS JOB_CATEGORY_ID

,*** AS JOB_CATEGORY_NAME

,[description]   AS JOB_DESCRIPTION  

,date_created   AS DATE_CREATED  

,date_modified   AS DATE_MODIFIED

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id

INNER JOIN sys.syslogins l ON l.sid = j.owner_sid

WHERE ***= @login_name

ORDER BY ***

DECLARE @job_owner NVARCHAR(32);

SET @job_owner='sa';

SELECT 'EXEC msdb.dbo.sp_update_job   @job_name=N''' +*** + ''', @owner_login_name=N''' + RTRIM(LTRIM(@job_owner)) + ''';' AS change_job_owner_cmd

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id

INNER JOIN sys.syslogins l ON l.sid = j.owner_sid

WHERE *** = @login_name

ORDER BY ***

SELECT '

USE   [master]

GO

DROP LOGIN   ' +   QUOTENAME(@login_name) +

'

GO

' AS drop_login_user;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

DECLARE @database_id INT;

DECLARE @database_name sysname;

DECLARE @cmdText NVARCHAR(MAX);

DECLARE @prc_text NVARCHAR(MAX);

DECLARE @RowIndex INT;

DECLARE @user_name NVARCHAR(128);

IF OBJECT_ID('TempDB.dbo.#databases')   IS NOT NULL

DROP TABLE dbo.#databases;

CREATE TABLE #databases

(

database_id   INT,

database_name   sysname

)

INSERT INTO #databases

SELECT database_id ,

name

FROM sys.databases

WHERE name NOT IN ( 'master', 'tempdb', 'model',   'msdb',

'distribution',   'ReportServer',

'ReportServerTempDB',   'YourSQLDba' )

AND state = 0; --state_desc=ONLINE

CREATE TABLE #removed_user

(

username   sysname

)

--开始循环每一个用户数据库（排除了上面相关数据库）

WHILE 1= 1

BEGIN

SELECT TOP 1 @database_name= database_name

FROM #databases

ORDER BY database_id;

IF   @@ROWCOUNT =0

BREAK;

SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)

SELECT @cmdText += 'INSERT INTO   #removed_user

SELECT name   FROM sys.sysusers

WHERE sid   NOT IN (SELECT sid FROM sys.syslogins WHERE isntname=1 AND name LIKE   ''GFG1%'')

AND   isntname=1 AND name NOT IN (''NT AUTHORITY\SYSTEM'')' + CHAR(10);

EXEC SP_EXECUTESQL @cmdText

SELECT @database_name AS database_name;

SELECT j.job_id AS JOB_ID

,*** AS JOB_NAME

,CASE WHEN [enabled] =1 THEN 'Enabled'

ELSE 'Disabled' END AS JOB_ENABLED

,*** AS JOB_OWNER

,j.category_id   AS JOB_CATEGORY_ID

,*** AS JOB_CATEGORY_NAME

,[description]   AS JOB_DESCRIPTION  

,date_created   AS DATE_CREATED  

,date_modified   AS DATE_MODIFIED

FROM msdb.dbo.sysjobs j

INNER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id

INNER JOIN sys.syslogins l ON l.sid = j.owner_sid

INNER JOIN #removed_user r ON *** = ***ername

ORDER BY ***;

SELECT *** AS database_name ,

*** AS database_owner ,

d.create_date   AS create_date   ,

***llation_name   AS collcation_name   ,

d.state_desc   AS state_desc

FROM sys.databases d

INNER JOIN sys.syslogins l ON d.owner_sid = l.sid

INNER JOIN #removed_user r ON ***ername = ***

SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)

SET @cmdText += 'SELECT * FROM   sys.schemas s

INNER JOIN   #removed_user r ON *** =***ername Collate Database_Default' + CHAR(10);

EXEC SP_EXECUTESQL @cmdText;

SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)

SET @cmdText += 'SELECT * FROM   sys.objects WHERE schema_id IN (SELECT s.schema_id FROM sys.schemas s INNER   JOIN #removed_user r ON *** =***ername Collate Database_Default);'

EXEC SP_EXECUTESQL @cmdText;

SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)

SET @cmdText += 'SELECT ''USE ' + @database_name + ';'' + CHAR(10)   +''GO'' + CHAR(10) +''ALTER AUTHORIZATION ON SCHEMA::'' +QUOTENAME(***)   +'' TO [dbo];'' AS change_schema_cmd FROM sys.schemas s

INNER JOIN   #removed_user r ON *** =***ername Collate Database_Default ' + CHAR(10);

EXEC SP_EXECUTESQL @cmdText,   N'@database_name sysname',@database_name ;

SET @cmdText = 'USE ' + @database_name + ';' +CHAR(10)

SET @cmdText += 'SELECT ''USE ' + @database_name + ';'' + CHAR(10)   +''GO'' + CHAR(10) +''ALTER AUTHORIZATION ON SCHEMA::''   +QUOTENAME(s.SCHEMA_NAME) +'' TO [dbo];'' AS change_schema_cmd

FROM   INFORMATION_SCHEMA.SCHEMATA s

INNER JOIN   #removed_user r ON s.SCHEMA_OWNER =***ername Collate Database_Default' + CHAR(10);

EXEC SP_EXECUTESQL @cmdText,   N'@database_name sysname',@database_name ;

SELECT 'USE ' + QUOTENAME(@database_name) +   CHAR(10)

+ 'GO ' + CHAR(10)

+ 'DROP   USER ' +   QUOTENAME(username) +';' + CHAR(10)

+ 'GO' AS drop_user_cmd

FROM #removed_user;

TRUNCATE TABLE #removed_user;

DELETE FROM #databases WHERE database_name=@database_name;

END

DROP TABLE #databases;

DROP TABLE #removed_user;