Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 12 August 2016

Fix Orphan users Manually one by one and Bulk Fix



Fix Orphan users Manually one by one and Bulk Fix 

Normally we use below script to fix orphan users one by one

Manual fix one by one:
sp_change_users_login 'report'

sp_change_users_login @Action='update_one', @UserNamePattern='TestA', @LoginName='TestA'

sp_change_users_login @Action='update_one', @UserNamePattern='TestB', @LoginName='TestB'

Bulk fix orphan users:

if you need to fix orphan users more than 10 logins then we have to execute below Script 1

Script 1:

USE master

DROP TABLE tblOrphanUsers

USE master

CREATE TABLE tblOrphanUsers

(username varchar(100),
UserSid varchar(max),
dbname varchar(200) default db_name()
)

/*Create Dynamic SQL to insert all the orphaned users from all the databases into the new table that was created*/

USE master

SELECT 'USE '+ name+ ' INSERT INTO master.dbo.tblOrphanUsers (UserName,UserSID) EXEC sp_change_users_login ''Report'' '

FROM sys.databases
WHERE name not in ('master','msdb','model','distribution','tempdb')
AND name IN ('dbname')
ORDER BY name


Script 2:

SELECT 'USE '+DBname+ ' EXEC sp_change_users_login ''UPDATE_ONE'','''+username+''','''+username+''' '
FROM master.dbo.tblOrphanUsers
where dbname='dbname'

Kindly execute Script 2 and copy output then open new Query window and paste it and execute it.

Script 3:
some times to copy entire logins from Source we can use  sp_helprevlogin
execute it on source and copy output and paste it on Destination and execute it.

script of sp_helprevlogin:

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  


Script 4:

To check login sid of master and user database matches or existing or not

USE MASTER
GO
SELECT name as SQLServerLogIn,SID as SQLServerSID FROM sys.syslogins
WHERE [name] = 'loginName'
GO

USE DatabaseName
GO
SELECT name DataBaseID,SID as DatabaseSID FROM sysusers
WHERE [name] = 'Loginname'


 

Tuesday, 9 August 2016

Database Mirroring in SQL Server 2005 across domains

Database Mirroring in SQL Server 2005 across domains

Database mirroring has become a very popular choice for a high availability solution in SQL Server 2005. And while Microsoft recommends using Windows Authentication for anything accessing SQL Server, there are cases where this is a limitation. A typical example would be a non-Microsoft application running on a non-Microsoft platform accessing a SQL Server database. Another would be SQL Servers spread across different geographical locations with no Active Directory to authenticate user access or member servers on different domains with no trust relationships. SQL Server 2005 enables us to configure database mirroring under mixed mode authentication using SQL Server logins with the added security of using certificates.
We will use the Northwind database to demonstrate the process. Remember to change Northwind's database recovery model toFULL before working through the steps below.
Let's call the principal server testServer1, the mirror server testServer2 and witness server testServer3. You can also do this without the witness although this tip will highlight using one.
1) Backup the principal database and restore it on the mirror server 
Perform a FULL and LOG backup for the Northwind database on the principal server.
--Run this on testServer1/principal server  USE master   
GO   
BACKUP DATABASE Northwind    
   
TO DISK = N'D:\DBBackup\NorthwindBackup.bak'   
   
WITH NAME N'Full Database Backup',   
    
INITSTATS 10   
GO   
BACKUP LOG Northwind    
   
TO DISK = N'D:\DBBackup\NorthwindBackupLog.trn'   
   
WITH NAME N'Transaction Log Backup',     
       
STATS 10   
GO  
After the backup has completed, restore it on the mirror server using the WITH NORECOVERY option.
--Run this on testServer2/mirror server  RESTORE DATABASE Northwind   FROM DISK = N'D:\DBBackup\NorthwindBackup.bak'   WITH NORECOVERY 
GO   
RESTORE LOG Northwind   FROM DISK = N'D:\DBBackup\NorthwindBackupLog.trn'   WITH NORECOVERY 
GO 
2) Validate your DNS entries
Chances are that you may have your DNS entries for the SQL Server instances properly configured. To validate, do a PING test on both hostnames of the servers running your SQL Server 2005 instances using it's fully qualified domain name (FQDN) in the formhostname.primary DNS suffix. You can retrieve the value of the primary DNS suffix by running an IPCONFIG /ALL in your command line. For servers in workgroups, this could be a bit of a challenge as you might not have a DNS server that would resolve the hostname to their appropriate IP addresses. You would have to modify your hosts file to manually add the IP address-hostname mappings. To do this, open the hosts file on your %systemroot%\system32\drivers\etc\ using any text editor. Enter the IP addresses and hostnames of your principal, mirror and witness servers as shown below
3) Create a database master key on the principal server
--Run this on the principal server/testServer1  USE master  
CREATE MASTER KEY ENCRYPTION BY PASSWORD 'y0ur$ecUr3PAssw0rd';  GO
4) Create the server-based certificate which will be used to encrypt the database mirroring endpoint
--Run this on the principal server/testServer1  CREATE CERTIFICATE testServer1_cert WITH SUBJECT 'testServer1 certificate for database mirroring'  GO 
5) Create the database mirroring endpoint for the principal server using the certificate for authentication
--Run this on the principal server/testServer1  CREATE ENDPOINT Endpoint_Mirroring STATE STARTED AS TCP(LISTENER_PORT 9999LISTENER_IP ALL)  FOR DATABASE_MIRRORING (AUTHENTICATION CERTIFICATE testServer1_cert,  ENCRYPTION REQUIRED ALGORITHM AESROLE ALL);  GO 
This script creates a database mirroring endpoint named Endpoint_Mirroring using port number 9999 and the certificate namedtestServer1_cert. You can use any encryption algorithm available in SQL Server 2005 a described in this Microsoft TechNet article. I just opted to use AES. You can validate that the endpoint has been created by querying the sys.endpoints catalog view
6) Export the certificate to a file
--Run this on the principal server/testServer1  BACKUP CERTIFICATE testServer1_cert TO FILE = 'C:\testServer1_cert.cer';  GO 
7) Copy the certificate file to the mirror and witness server
You need to copy the certificate file to both the mirror and the witness server as we will use this certificate to associate a login for authentication. For this example, all certificates will be copied to the C:\ drive on all servers
Steps #3 to #7 should be repeated on both mirror and witness server, modifying the certificate name while keeping the other configurations.
A complete script for the mirror server is shown below
--Run this on testServer2  

--STEP #3:  
USE master  
CREATE MASTER KEY ENCRYPTION BY PASSWORD 'y0ur$ecUr3PAssw0rd';  GO 
--STEP #4:  CREATE CERTIFICATE testServer2_cert WITH SUBJECT 'testServer2 certificate for database mirroring'  GO  
--STEP #5:  CREATE ENDPOINT Endpoint_Mirroring STATE STARTED  AS TCP(LISTENER_PORT 9999LISTENER_IP ALL)  FOR DATABASE_MIRRORING (AUTHENTICATION CERTIFICATE testServer2_cert,  ENCRYPTION REQUIRED ALGORITHM AESROLE ALL);  GO  
--STEP #6:  BACKUP CERTIFICATE testServer2_cert TO FILE = 'C:\testServer2_cert.cer';  GO 
--STEP #7: Copy the certificate to the principal and witness servers 
A complete script for the witness server is shown below
--Run this on testServer3  

--STEP #3:  
USE master  
CREATE MASTER KEY ENCRYPTION BY PASSWORD 'y0ur$ecUr3PAssw0rd';  GO 
--STEP #4:  CREATE CERTIFICATE testServer3_cert WITH SUBJECT 'testServer3 certificate for database mirroring'  GO  
--STEP #5:  CREATE ENDPOINT Endpoint_Mirroring STATE STARTED  AS TCP(LISTENER_PORT 9999LISTENER_IP ALL)  FOR DATABASE_MIRRORING (AUTHENTICATION CERTIFICATE testServer3_cert,  ENCRYPTION REQUIRED ALGORITHM AESROLE ALL);  GO  
--STEP #6:  BACKUP CERTIFICATE testServer3_cert TO FILE = 'C:\testServer3_cert.cer';  GO 
--STEP #7: Copy the certificate to the principal and mirror servers 
8) Create a login on the principal for the mirror
For simplicity's sake we shall use the same name for the login on all servers to identify that it is used primarily for the database mirroring sessions
--Run this on testServer1 
USE master;  CREATE LOGIN login_mirroring WITH PASSWORD 'y0ur$ecUr3PAssw0rd';  GO 
9) Create a user for the login
--Run this on testServer1 
CREATE USER login_mirroring  FOR LOGIN login_mirroring  
GO 
10) Associate the certificate we created in Step #4 with the user
This script will associate the certificate we created for the mirror server - testServer2_cert.cer - to the user login_mirroring
--Run this on testServer1 
CREATE CERTIFICATE testServer2_cert  AUTHORIZATION login_mirroring  FROM FILE = 'C:\testServer2_cert.cer'  GO 
We'll do the same thing for the certificate we created for the witness server - testServer3_cert.cer - to the user login_mirroring
--Run this on testServer1 
CREATE CERTIFICATE testServer3_cert  AUTHORIZATION login_mirroring  FROM FILE = 'C:\testServer3_cert.cer'  GO 
11) Grant the CONNECT permission on the login
This script will grant the CONNECT permission to the login login_mirroring for the database mirroring endpoint
--Run this on testServer1 
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring];  GO 
Since we used the same name for the logins on all servers, you only need to do this once. If you are using a different name for the logins, you will have to do this for each login. The key thing to remember here is that the logins you use to connect to any of the SQL Server 2005 instances are granted the CONNECT permissions with the appropriate certificates.
We will do the exact same thing on both the mirror and witness servers.
A complete script for the mirror server is shown below
--Run this on testServer2 

--STEP #8:  
USE master;  CREATE LOGIN login_mirroring WITH PASSWORD 'y0ur$ecUr3PAssw0rd';  GO 
--STEP #9: CREATE USER login_mirroring  FOR LOGIN login_mirroring  
GO 
--STEP #10: CREATE CERTIFICATE testServer1_cert  AUTHORIZATION login_mirroring  FROM FILE = 'C:\testServer1_cert.cer'  GO 
CREATE CERTIFICATE testServer3_cert  AUTHORIZATION login_mirroring  FROM FILE = 'C:\testServer3_cert.cer'  GO 
--STEP #11: GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring];  GO
A complete script for the witness server is shown below
--Run this on testServer3 

--STEP #8:  
USE master;  CREATE LOGIN login_mirroring WITH PASSWORD 'y0ur$ecUr3PAssw0rd';  GO 
--STEP #9: CREATE USER login_mirroring  FOR LOGIN login_mirroring  
GO 
--STEP #10: CREATE CERTIFICATE testServer1_cert  AUTHORIZATION login_mirroring  FROM FILE = 'C:\testServer1_cert.cer'  GO 
CREATE CERTIFICATE testServer2_cert  AUTHORIZATION login_mirroring  FROM FILE = 'C:\testServer2_cert.cer'  GO 
--STEP #11: GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [login_mirroring];  GO
12) Prepare the mirror server for the database mirroring session
This script prepares the mirror database for the database mirroring session, specifying testServer1 as the partner. It is important to run this first on the mirror server before running the equivalent script on the principal server. The hostname specified here should be resolved using the approach you used in Step #2
--Run this on testServer2  USE master  
GO  
ALTER DATABASE Northwind SET PARTNER 'TCP://testServer1:9999';  GO 
13) Prepare the principal server for the database mirroring session
Now that the mirror database has been prepared for the database mirroring session, we will do the same for the principal database specifying testServer2 as the partner.
--Run this on testServer1  USE master  
GO  
ALTER DATABASE Northwind SET PARTNER 'TCP://testServer2:9999';  GO 
We will also specify testServer3 as the witness.
--Run this on testServer1  USE master  
GO  
ALTER DATABASE Northwind SET WITNESS 'TCP://testServer3:9999';  GO 
14) Validate the database mirroring configuration using Database Mirroring Monitor
Run the Database Mirroring Monitor to check the status of the database mirroring sessions.
Ref:
https://www.mssqltips.com/sqlservertip/1705/implementing-database-mirroring-in-sql-server-2005-across-domains/

How to Change the server name for a SQL Server machine

Steps to change the server name for a SQL Server machine

As you may know, the internal SQL Server name is based on the physical server name, but changing the physical server name does not update the metadata within SQL Server.
When connecting to SQL Server, we use the physical server name for the default instance and physical server name\INSTANCE Name for a named instance. If you change the physical server name, there is no need to re-install SQL Server to reset the physical server name. You can follow the below steps to update the SQL Server system metadata which stores the physical server name in sys.servers.
The below solution works for default and named SQL instances on a standalone machine (non-clustered).  Also, you need to follow the steps for each SQL Server instance on the machine.

Pre Update Steps

Check for Remote Logins

  • If remote logins exist you have to drop them first, if you do not drop the remote logins you will get the below error when executing the sp_dropserver stored procedure.
Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 56
There are still remote logins or linked logins for the server 'sqldbpool'.
  • You can run this query to get a list of remote logins that exist on the server.
-- Query to check remote login
select 
srl.remote_name as RemoteLoginName, 
sss.srvname
from sys.remote_logins srl join sys.sysservers sss on srl.server_id = sss.srvid
-- Query to remove the remote login
--Default Instance
sp_dropremotelogin old_physical_server_name
GO
--Named Instance
sp_dropremotelogin 'old_physical_server_name\instancename'
GO

Check for Replication

  • SQL Server does not support renaming computers that are involved in replication. If the SQL Server is involved in replication you have to remove it first.  If you do not remove replication, you will get the below error when updating SQL Server System metadata.
Msg 15185, Level 16, State 1, Procedure sp_dropremotelogin, Line 68
There is no remote user '(null)' mapped to local user '(null)' from the remote 
server 'sqldbpool'.
  • You can run this query to see if replication is in place.  If so, you could script out the settings first so you can recreate afterwards and then remove replication.
-- Query to check if the SQL Instance databases are involved in replication
select name,is_published,is_subscribed,is_distributor from sys.databases
where is_published = 1 or is_subscribed =1 or is_distributor = 1
GO

Check for Reporting Services

  • If SQL Server Reporting Service is installed and configured, you have to first stop the Reporting Services service.
sql server configuration manager

Check for Database Mirroring

  • If database mirroring is enabled you have to remove mirroring and then re-establish mirroring once you have made the system metadata update.
--Query to find out the databases involved in DB mirroring
select database_id, db_name(database_id)
from sys.database_mirroring where mirroring_guid is not null
--Query to turn off the database mirroring
ALTER DATABASE SET PARTNER OFF

Steps to rename SQL Server

  •  Execute the below commands for each instance on the machine.
-- for a Default Instance
sp_dropserver 
GO
sp_addserver , local
GO

--for a Named Instance
sp_dropserver <'old_physical_server_name\instancename'>
GO
sp_addserver <'new_name\instancename'>, local
GO
  •  Restart the SQL Server services.
  •  Execute the below query in each instance to verify the updated system metadata.
SELECT @@SERVERNAME

Post Update Steps

  • Add the remote logins using the sp_addremotelogin command.
sp_addremotelogin [ @remoteserver = ] 'remoteserver' 
     [ , [ @loginame = ] 'login' ] 
          [ , [ @remotename = ] 'remote_name' ]
 
  • Reconfigure Replication if this was setup.
  • Reconfigure Database Mirroring if this was setup.
  • Reconfigure Reporting Services if this was setup and connect to the new server name as shown below.
reporting services configuration manager
  • If you also changed the Report Server computer name, you will have to edit the RSReportServer.config file and update theURLRoot setting.  Do to this, open the RSWebApplication.config file and modify the ReportServerUrl setting to reflect the new server name.
  • Change application connection strings pointing to the server
  • Change linked servers pointing to the server
Ref:
https://www.mssqltips.com/sqlservertip/2525/steps-to-change-the-server-name-for-a-sql-server-machine/