{"id":115,"date":"2014-04-23T12:09:59","date_gmt":"2014-04-23T08:09:59","guid":{"rendered":"http:\/\/ustinych.net\/wordpress\/?p=115"},"modified":"2014-05-12T18:52:59","modified_gmt":"2014-05-12T14:52:59","slug":"%d0%bf%d0%b5%d1%80%d0%b5%d0%bc%d0%b5%d1%89%d0%b5%d0%bd%d0%b8%d0%b5-%d0%b8%d0%bc%d0%b5%d0%bd-%d0%b2%d1%85%d0%be%d0%b4%d0%b0-%d0%b8-%d0%bf%d0%b0%d1%80%d0%be%d0%bb%d0%b5%d0%b9-%d0%bc%d0%b5%d0%b6%d0%b4","status":"publish","type":"post","link":"https:\/\/ustinych.net\/wordpress\/?p=115","title":{"rendered":"\u041f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0438\u0435 \u0438\u043c\u0435\u043d \u0432\u0445\u043e\u0434\u0430 \u0438 \u043f\u0430\u0440\u043e\u043b\u0435\u0439 \u043c\u0435\u0436\u0434\u0443 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430\u043c\u0438 SQL Server 2005"},"content":{"rendered":"

\u0412\u0432\u0435\u0434\u0435\u043d\u0438\u0435<\/span><\/h1>\n
\u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u043e\u043f\u0438\u0441\u0430\u043d\u043e, \u043a\u0430\u043a \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0430\u0442\u044c \u0438\u043c\u0435\u043d\u0430 \u0432\u0445\u043e\u0434\u0430 \u0438 \u043f\u0430\u0440\u043e\u043b\u0438 \u043c\u0435\u0436\u0434\u0443 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430\u043c\u0438 Microsoft SQL Server 2005 \u043d\u0430 \u0440\u0430\u0437\u043b\u0438\u0447\u043d\u044b\u0445 \u0441\u0435\u0440\u0432\u0435\u0440\u0430\u0445.\u0414\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u044b\u0435 \u0441\u0432\u0435\u0434\u0435\u043d\u0438\u044f \u043e \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0438\u0438 \u0438\u043c\u0435\u043d \u0432\u0445\u043e\u0434\u0430 \u0438 \u043f\u0430\u0440\u043e\u043b\u0435\u0439 \u043c\u0435\u0436\u0434\u0443 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430\u043c\u0438 \u0434\u0440\u0443\u0433\u0438\u0445 \u0432\u0435\u0440\u0441\u0438\u0439 SQL Server \u0441\u043c. \u0432 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u0431\u0430\u0437\u044b \u0437\u043d\u0430\u043d\u0438\u0439 \u041c\u0430\u0439\u043a\u0440\u043e\u0441\u043e\u0444\u0442:<\/p>\n
246133<\/a>\u00a0\u041f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0438\u0435 \u0438\u043c\u0435\u043d \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0435\u0439 \u0438 \u043f\u0430\u0440\u043e\u043b\u0435\u0439 \u043c\u0435\u0436\u0434\u0443 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430\u043c\u0438 SQL Server.<\/div>\n<\/div>\n

\u0414\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u0430\u044f \u0438\u043d\u0444\u043e\u0440\u043c\u0430\u0446\u0438\u044f<\/span><\/h1>\n
\u0412 \u044d\u0442\u043e\u0439 \u0441\u0442\u0430\u0442\u044c\u0435 \u0441\u0435\u0440\u0432\u0435\u0440\u044b A \u0438 B \u044f\u0432\u043b\u044f\u044e\u0442\u0441\u044f \u0440\u0430\u0437\u043b\u0438\u0447\u043d\u044b\u043c\u0438 \u0441\u0435\u0440\u0432\u0435\u0440\u0430\u043c\u0438. \u041a\u0430\u043a \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 A, \u0442\u0430\u043a \u0438 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 \u0438 B \u0432\u044b\u043f\u043e\u043b\u043d\u044f\u0435\u0442\u0441\u044f SQL Server 2005.\u041f\u043e\u0441\u043b\u0435 \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0438\u044f \u0431\u0430\u0437\u044b \u0434\u0430\u043d\u043d\u044b\u0445 \u0441 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430 SQL Server \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 A \u0432 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440 SQL Server \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 B, \u0432\u043e\u0437\u043c\u043e\u0436\u043d\u043e, \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u0438 \u043d\u0435 \u0441\u043c\u043e\u0433\u0443\u0442 \u0432\u043e\u0439\u0442\u0438 \u0432 \u0431\u0430\u0437\u0443 \u0434\u0430\u043d\u043d\u044b\u0445 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 B. \u041a\u0440\u043e\u043c\u0435 \u0442\u043e\u0433\u043e, \u043c\u043e\u0436\u0435\u0442 \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0442\u044c \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0435\u0435 \u0441\u043e\u043e\u0431\u0449\u0435\u043d\u0438\u0435 \u043e\u0431 \u043e\u0448\u0438\u0431\u043a\u0435:<\/p>\n
\n
\n
\u041e\u0448\u0438\u0431\u043a\u0430 \u0432\u0445\u043e\u0434\u0430 \u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044f «\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u0435\u043b\u044c<\/var>«. (Microsoft SQL Server, \u043e\u0448\u0438\u0431\u043a\u0430: 18456)<\/div>\n<\/div>\n<\/div>\n

\u042d\u0442\u0430 \u043f\u0440\u043e\u0431\u043b\u0435\u043c\u0430 \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u0435\u0442 \u0438\u0437-\u0437\u0430 \u0442\u043e\u0433\u043e, \u0447\u0442\u043e \u0438\u043c\u0435\u043d\u0430 \u0432\u0445\u043e\u0434\u0430 \u0438 \u043f\u0430\u0440\u043e\u043b\u0438 \u043d\u0435 \u0431\u044b\u043b\u0438 \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u044b \u0438\u0437 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0430 SQL Server \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 A \u0432 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440 SQL Server \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 B.<\/p>\n

\u0414\u043b\u044f \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0438\u044f \u0438\u043c\u0435\u043d \u0432\u0445\u043e\u0434\u0430 \u0438 \u043f\u0430\u0440\u043e\u043b\u0435\u0439 \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u0435 \u0443\u043a\u0430\u0437\u0430\u043d\u043d\u044b\u0435 \u043d\u0438\u0436\u0435 \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u044f.<\/p>\n

    \n
  1. \u041d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 A \u0437\u0430\u043f\u0443\u0441\u0442\u0438\u0442\u0435 SQL Server Management Studio \u0438 \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u0435\u0441\u044c \u043a \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0443 SQL Server, \u0438\u0437 \u043a\u043e\u0442\u043e\u0440\u043e\u0433\u043e \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0430 \u0431\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445.<\/li>\n
  2. \u041e\u0442\u043a\u0440\u043e\u0439\u0442\u0435 \u043e\u043a\u043d\u043e \u0440\u0435\u0434\u0430\u043a\u0442\u043e\u0440\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u0435 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0439:\n
    \n
    \n
    USE master\r\nGO\r\nIF OBJECT_ID ('sp_hexadecimal') IS NOT NULL\r\n  DROP PROCEDURE sp_hexadecimal\r\nGO\r\nCREATE PROCEDURE sp_hexadecimal\r\n    @binvalue varbinary(256),\r\n    @hexvalue varchar (514) OUTPUT\r\nAS\r\nDECLARE @charvalue varchar (514)\r\nDECLARE @i int\r\nDECLARE @length int\r\nDECLARE @hexstring char(16)\r\nSELECT @charvalue = '0x'\r\nSELECT @i = 1\r\nSELECT @length = DATALENGTH (@binvalue)\r\nSELECT @hexstring = '0123456789ABCDEF'\r\nWHILE (@i <= @length)\r\nBEGIN\r\n  DECLARE @tempint int\r\n  DECLARE @firstint int\r\n  DECLARE @secondint int\r\n  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))\r\n  SELECT @firstint = FLOOR(@tempint\/16)\r\n  SELECT @secondint = @tempint - (@firstint*16)\r\n  SELECT @charvalue = @charvalue +\r\n    SUBSTRING(@hexstring, @firstint+1, 1) +\r\n    SUBSTRING(@hexstring, @secondint+1, 1)\r\n  SELECT @i = @i + 1\r\nEND\r\n\r\nSELECT @hexvalue = @charvalue\r\nGO\r\n \r\nIF OBJECT_ID ('sp_help_revlogin') IS NOT NULL\r\n  DROP PROCEDURE sp_help_revlogin\r\nGO\r\nCREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS\r\nDECLARE @name sysname\r\nDECLARE @type varchar (1)\r\nDECLARE @hasaccess int\r\nDECLARE @denylogin int\r\nDECLARE @is_disabled int\r\nDECLARE @PWD_varbinary  varbinary (256)\r\nDECLARE @PWD_string  varchar (514)\r\nDECLARE @SID_varbinary varbinary (85)\r\nDECLARE @SID_string varchar (514)\r\nDECLARE @tmpstr  varchar (1024)\r\nDECLARE @is_policy_checked varchar (3)\r\nDECLARE @is_expiration_checked varchar (3)\r\n\r\nDECLARE @defaultdb sysname\r\n \r\nIF (@login_name IS NULL)\r\n  DECLARE login_curs CURSOR FOR\r\n\r\n      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM \r\nsys.server_principals p LEFT JOIN sys.syslogins l\r\n      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'\r\nELSE\r\n  DECLARE login_curs CURSOR FOR\r\n\r\n\r\n      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM \r\nsys.server_principals p LEFT JOIN sys.syslogins l\r\n      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name\r\nOPEN login_curs\r\n\r\nFETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin\r\nIF (@@fetch_status = -1)\r\nBEGIN\r\n  PRINT '\u0418\u043c\u0435\u043d\u0430 \u043d\u0435 \u043d\u0430\u0439\u0434\u0435\u043d\u044b.'\r\n  CLOSE login_curs\r\n  DEALLOCATE login_curs\r\n  RETURN -1\r\nEND\r\nSET @tmpstr = '\/* sp_help_revlogin script '\r\nPRINT @tmpstr\r\nSET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' *\/'\r\nPRINT @tmpstr\r\nPRINT ''\r\nWHILE (@@fetch_status <> -1)\r\nBEGIN\r\n  IF (@@fetch_status <> -2)\r\n  BEGIN\r\n    PRINT ''\r\n    SET @tmpstr = '-- Login: ' + @name\r\n    PRINT @tmpstr\r\n    IF (@type IN ( 'G', 'U'))\r\n    BEGIN -- NT authenticated account\/group\r\n\r\n      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'\r\n    END\r\n    ELSE BEGIN -- SQL Server authentication\r\n        -- obtain password and sid\r\n            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )\r\n        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT\r\n        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT\r\n \r\n        -- obtain password policy state\r\n        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\r\n        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\r\n \r\n            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'\r\n\r\n        IF ( @is_policy_checked IS NOT NULL )\r\n        BEGIN\r\n          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked\r\n        END\r\n        IF ( @is_expiration_checked IS NOT NULL )\r\n        BEGIN\r\n          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked\r\n        END\r\n    END\r\n    IF (@denylogin = 1)\r\n    BEGIN -- login is denied access\r\n      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )\r\n    END\r\n    ELSE IF (@hasaccess = 0)\r\n    BEGIN -- login exists but does not have access\r\n      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )\r\n    END\r\n    IF (@is_disabled = 1)\r\n    BEGIN -- login is disabled\r\n      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'\r\n    END\r\n    PRINT @tmpstr\r\n  END\r\n\r\n  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin\r\n   END\r\nCLOSE login_curs\r\nDEALLOCATE login_curs\r\nRETURN 0\r\nGO\r\n<\/pre>\n<\/div>\n<\/div>\n
    \u041f\u0440\u0438\u043c\u0435\u0447\u0430\u043d\u0438\u0435<\/b>. \u0421\u0446\u0435\u043d\u0430\u0440\u0438\u0439 \u0441\u043e\u0437\u0434\u0430\u0435\u0442 \u0432 \u0431\u0430\u0437\u0435 \u0434\u0430\u043d\u043d\u044b\u0445\u00a0master<\/b>\u00a0\u0434\u0432\u0435 \u0445\u0440\u0430\u043d\u0438\u043c\u044b\u0445 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u044b\u00a0sp_hexadecimal<\/b>\u00a0\u0438\u00a0sp_help_revlogin<\/b>.<\/li>\n
  3. \u0412\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u0435 \u0441\u043b\u0435\u0434\u0443\u044e\u0449\u0438\u0439 \u043e\u043f\u0435\u0440\u0430\u0442\u043e\u0440:\n
    \n
    \n
    EXEC sp_help_revlogin<\/pre>\n<\/div>\n<\/div>\n
    \u0421\u0446\u0435\u043d\u0430\u0440\u0438\u0439, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u0441\u043e\u0437\u0434\u0430\u0435\u0442\u0441\u044f \u0445\u0440\u0430\u043d\u0438\u043c\u043e\u0439 \u043f\u0440\u043e\u0446\u0435\u0434\u0443\u0440\u043e\u0439\u00a0sp_help_revlogin<\/b>, \u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0435\u043c \u0432\u0445\u043e\u0434\u0430. \u042d\u0442\u043e\u0442 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0439 \u0441\u043e\u0437\u0434\u0430\u0435\u0442 \u0438\u043c\u0435\u043d\u0430 \u0432\u0445\u043e\u0434\u0430 \u0441 \u0438\u0441\u0445\u043e\u0434\u043d\u044b\u043c \u0438\u0434\u0435\u043d\u0442\u0438\u0444\u0438\u043a\u0430\u0442\u043e\u0440\u043e\u043c (\u0418\u0414) \u0431\u0435\u0437\u043e\u043f\u0430\u0441\u043d\u043e\u0441\u0442\u0438 \u0438 \u043f\u0430\u0440\u043e\u043b\u0435\u043c.<\/li>\n
  4. \u041d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 B \u0437\u0430\u043f\u0443\u0441\u0442\u0438\u0442\u0435 SQL Server Management Studio \u0438 \u043f\u043e\u0434\u043a\u043b\u044e\u0447\u0438\u0442\u0435\u0441\u044c \u043a \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0443 SQL Server, \u043d\u0430 \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043f\u0435\u0440\u0435\u043c\u0435\u0449\u0435\u043d\u0430 \u0431\u0430\u0437\u0430 \u0434\u0430\u043d\u043d\u044b\u0445.\u0412\u0430\u0436\u043d\u043e<\/b>! \u041f\u0435\u0440\u0435\u0434 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435\u043c \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u044f 5 \u043f\u0440\u043e\u0447\u0442\u0438\u0442\u0435 \u0440\u0430\u0437\u0434\u0435\u043b «\u041f\u0440\u0438\u043c\u0435\u0447\u0430\u043d\u0438\u044f».<\/li>\n
  5. \u041e\u0442\u043a\u0440\u043e\u0439\u0442\u0435 \u043d\u043e\u0432\u043e\u0435 \u043e\u043a\u043d\u043e \u0440\u0435\u0434\u0430\u043a\u0442\u043e\u0440\u0430 \u0437\u0430\u043f\u0440\u043e\u0441\u043e\u0432 \u0438 \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u0435 \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u0439, \u0441\u043e\u0437\u0434\u0430\u043d\u043d\u044b\u0439 \u0432 \u0434\u0435\u0439\u0441\u0442\u0432\u0438\u0438 3.<\/li>\n<\/ol>\n
    \u041f\u0440\u0438\u043c\u0435\u0447\u0430\u043d\u0438\u044f<\/h1>\n
    \u041f\u0435\u0440\u0435\u0434 \u0432\u044b\u043f\u043e\u043b\u043d\u0435\u043d\u0438\u0435\u043c \u0432\u044b\u0445\u043e\u0434\u043d\u043e\u0433\u043e \u0441\u0446\u0435\u043d\u0430\u0440\u0438\u044f \u0432 \u044d\u043a\u0437\u0435\u043c\u043f\u043b\u044f\u0440\u0435 \u043d\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u0435 B \u043f\u0440\u043e\u0447\u0442\u0438\u0442\u0435 \u0441\u0432\u0435\u0434\u0435\u043d\u0438\u044f, \u043f\u0440\u0435\u0434\u0441\u0442\u0430\u0432\u043b\u0435\u043d\u043d\u044b\u0435 \u043d\u0438\u0436\u0435.<\/p>\n