31
2013
How to transfer logins and passwords between instances of SQL Server 2008
How to transfer logins and passwords between instances of SQL Server 2008How to transfer logins and passwords between instances of SQL Server 2008 1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database. 2. Open a new Query Editor window, and then run the following script.
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 128 129 130 131 132 133 134 135 |
USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state 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 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 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO |
3. Note This script creates two stored procedures in the master database. The procedures […]
6
2010
Useful resources for migrating to SQL Server 2005
Looking for SQL Server 2000 to SQl Server 2005 migration, useful resources available at following link: http://blogs.technet.com/b/vipulshah/archive/2006/11/30/useful-resources-to-pitch-for-migrating-to-sql-server-2005.aspx Comparing Enterprise Development Productivity: SQL Server 2005 versus SQL Server 2000 http://www.microsoft.com/sql/prodinfo/compare/devprod2000.mspx “This independent productivity study, conducted by 3 Leaf, compares two developers working independently to provide the same added features and functionality to a baseline application. One developer worked with SQL Server 2005, and the other used SQL Server 2000. Bycomparing the time required by each developer […]
Subscribe to this blog via Email
Old Posts
- November 2017 (3)
- October 2017 (4)
- September 2017 (2)
- May 2017 (1)
- April 2017 (1)
- July 2016 (3)
- May 2016 (1)
- April 2016 (1)
- February 2016 (2)
- January 2016 (1)
- October 2015 (1)
- September 2015 (1)
- August 2015 (1)
- July 2015 (2)
- June 2015 (3)
- April 2015 (1)
- March 2015 (1)
- December 2014 (1)
- September 2014 (2)
- April 2014 (1)
- January 2014 (3)
- October 2013 (2)
- September 2013 (2)
- August 2013 (4)
- July 2013 (1)
- June 2013 (2)
- May 2013 (5)
- April 2013 (3)
- March 2013 (1)
- February 2013 (9)
- January 2013 (11)
- December 2012 (14)
- November 2012 (3)
- October 2012 (4)
- July 2012 (2)
- June 2012 (3)
- May 2012 (2)
- April 2012 (8)
- March 2012 (6)
- February 2012 (3)
- January 2012 (1)
- December 2011 (5)
- November 2011 (8)
- October 2011 (5)
- September 2011 (3)
- August 2011 (3)
- July 2011 (3)
- May 2011 (1)
- November 2010 (1)
Tags
Calender
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
« Nov | ||||||
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 |
View Post by Categories
Recent Articles
- Setting up Always ON Availability Group in Multi Subnet Cluster – Recommendations
- Configuring Replication with Always ON Availability Group
- Login failed for user ‘DOMAIN\COMPUTER$’. Reason: Could not find a login matching the name provided. [CLIENT: ]
- Modern Servicing Model (Service Pack and Cumulative Updates) for SQL Server 2017 and onwards
- Fix: SSMS 2012 opening Debug window when pressing F5