001.USE master
002.GO
003.IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
004. DROP PROCEDURE sp_hexadecimal
005.GO
006.CREATE PROCEDURE sp_hexadecimal
007. @binvalue varbinary(256),
008. @hexvalue varchar (514) OUTPUT
009.AS
010.DECLARE @charvalue varchar (514)
011.DECLARE @i int
012.DECLARE @length int
013.DECLARE @hexstring char(16)
014.SELECT @charvalue = '0x'
015.SELECT @i = 1
016.SELECT @length = DATALENGTH (@binvalue)
017.SELECT @hexstring = '0123456789ABCDEF'
018.WHILE (@i <= @length)
019.BEGIN
020. DECLARE @tempint int
021. DECLARE @firstint int
022. DECLARE @secondint int
023. SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
024. SELECT @firstint = FLOOR(@tempint/16)
025. SELECT @secondint = @tempint - (@firstint*16)
026. SELECT @charvalue = @charvalue +
027. SUBSTRING(@hexstring, @firstint+1, 1) +
028. SUBSTRING(@hexstring, @secondint+1, 1)
029. SELECT @i = @i + 1
030.END
031.
032.SELECT @hexvalue = @charvalue
033.GO
034.
035.IF OBJECT_ID ('sp_help_revlogin_with_roles') IS NOT NULL
036. DROP PROCEDURE sp_help_revlogin_with_roles
037.GO
038.CREATE PROCEDURE sp_help_revlogin_with_roles @login_name sysname = NULL AS
039.DECLARE @name sysname
040.DECLARE @type varchar (1)
041.DECLARE @hasaccess int
042.DECLARE @denylogin int
043.DECLARE @is_disabled int
044.DECLARE @PWD_varbinary varbinary (256)
045.DECLARE @PWD_string varchar (514)
046.DECLARE @SID_varbinary varbinary (85)
047.DECLARE @SID_string varchar (514)
048.DECLARE @tmpstr varchar (1024)
049.DECLARE @is_policy_checked varchar (3)
050.DECLARE @is_expiration_checked varchar (3)
051.
052.DECLARE @defaultdb sysname
053.
054.DECLARE @srvrolemember sysname
055.DECLARE @str varchar(max)
056.
057.IF (@login_name IS NULL)
058. DECLARE login_curs CURSOR FOR
059.
060. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
061.sys.server_principals p LEFT JOIN sys.syslogins l
062. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
063.ELSE
064. DECLARE login_curs CURSOR FOR
065.
066. SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
067.sys.server_principals p LEFT JOIN sys.syslogins l
068. ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
069.OPEN login_curs
070.
071.FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
072.IF (@@fetch_status = -1)
073.BEGIN
074. PRINT 'Имена не найдены.'
075. CLOSE login_curs
076. DEALLOCATE login_curs
077. RETURN -1
078.END
079.SET @tmpstr = '/* sp_help_revlogin script '
080.PRINT @tmpstr
081.SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
082.PRINT @tmpstr
083.PRINT ''
084.WHILE (@@fetch_status <> -1)
085.BEGIN
086. IF (@@fetch_status <> -2)
087. BEGIN
088. PRINT ''
089. SET @tmpstr = '-- Login: ' + @name
090. PRINT @tmpstr
091.
092. SET @tmpstr = 'IF EXISTS (SELECT * FROM sys.server_principals WHERE name= ' + QUOTENAME( @name , '''') + ' ) DROP LOGIN ' + QUOTENAME( @name ) +';'
093. PRINT @tmpstr
094.
095. IF (@type IN ( 'G', 'U'))
096. BEGIN
097.
098. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
099. END
100. ELSE BEGIN
101.
102. SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
103. EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
104. EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
105.
106.
107. SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
108. SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
109.
110. SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
111.
112. IF ( @is_policy_checked IS NOT NULL )
113. BEGIN
114. SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
115. END
116. IF ( @is_expiration_checked IS NOT NULL )
117. BEGIN
118. SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
119. END
120. END
121. IF (@denylogin = 1)
122. BEGIN
123. SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
124. END
125. ELSE IF (@hasaccess = 0)
126. BEGIN
127. SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
128. END
129. IF (@is_disabled = 1)
130. BEGIN
131. SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE;'
132. END
133. PRINT @tmpstr
134. END
135.
136.
137. DECLARE srvrolemember_curs CURSOR FOR
138. SELECT r.name FROM sys.server_role_members rm
139. INNER JOIN
140. sys.server_principals r ON rm.role_principal_id=r.principal_id
141. INNER JOIN
142. sys.server_principals p ON rm.member_principal_id=p.principal_id
143. WHERE p.name=@name
144. OPEN srvrolemember_curs
145. FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember
146. WHILE (@@fetch_status <> -1)
147. BEGIN
148. IF (@@fetch_status <> -2)
149. BEGIN
150. SET @tmpstr = 'EXEC sp_addsrvrolemember ' + QUOTENAME( @name ) + ', '+ QUOTENAME( @srvrolemember ) + ';'
151. PRINT @tmpstr
152. END
153. FETCH NEXT FROM srvrolemember_curs INTO @srvrolemember
154. END
155. CLOSE srvrolemember_curs
156. DEALLOCATE srvrolemember_curs
157.
158.
159. set @str='USE ?
160. IF EXISTS (SELECT * FROM sys.database_principals WHERE sid='+
161. CONVERT (VARCHAR(514), @SID_varbinary, 1)+')
162. BEGIN
163. DECLARE @name sysname, @schema sysname
164. SELECT @name=name, @schema=default_schema_name FROM sys.database_principals WHERE sid='+
165. CONVERT (VARCHAR(514), @SID_varbinary, 1)+
166. 'print "USE ?;
167. IF EXISTS (SELECT * FROM sys.database_principals WHERE name=''"+@name+"'')
168. DROP USER "+QUOTENAME(@name)+";
169. CREATE USER "+QUOTENAME(@name)+" FOR LOGIN '+ QUOTENAME( @name ) +' WITH DEFAULT_SCHEMA = "+QUOTENAME(@schema)+";"
170.
171. DECLARE @dbrolemember sysname
172. DECLARE dbrolemember_curs CURSOR FOR
173. SELECT r.name FROM sys.database_role_members rm
174. INNER JOIN
175. sys.database_principals r ON rm.role_principal_id=r.principal_id
176. INNER JOIN
177. sys.database_principals p ON rm.member_principal_id=p.principal_id
178. WHERE p.name=@name
179. OPEN dbrolemember_curs
180. FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember
181. WHILE (@@fetch_status <> -1)
182. BEGIN
183. IF (@@fetch_status <> -2)
184. BEGIN
185. PRINT "EXEC sp_addrolemember "+QUOTENAME(@dbrolemember)+", "+QUOTENAME(@name)+";"
186. END
187. FETCH NEXT FROM dbrolemember_curs INTO @dbrolemember
188. END
189. CLOSE dbrolemember_curs
190. DEALLOCATE dbrolemember_curs
191. END
192. '
193. EXECUTE sp_MSforeachdb @str
194.
195. FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
196. END
197.CLOSE login_curs
198.DEALLOCATE login_curs
199.RETURN 0
200.GO
