Script to extract user permissions info from a database prior to migration

–Extract db users
— before running this script, you should fix orphaned users first
SELECT name
,type_desc
,’IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ”’ + name + ”’)
Exec sp_grantdbaccess ”’+suser_sname(sid)+”’, ”’+name +”” [Command to Add DB User],
sid
from sys.database_principals
where principal_id>4 and type in(‘S’, ‘U’ , ‘G’)
AND suser_sname(sid) IS NOT NULL — this is just a check just in case there are orphaned
users

— Extract user roles in database
SELECT db_name() [DatabaseName]
,name [RoleName]
,type_desc [RoleType]
,’IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ”’ + name + ”’
and type = ”R”)
Exec sp_addRole ”’ + name +”” [Command To Create Role]
from sys.database_principals where type in(‘R’,’A’)
and name<>’public’ and is_fixed_role<>1

–Extract database role membership
select user_name(DRM.member_principal_id) [DatabaseUser]
,user_name(DRM.role_principal_id) [DatabaseRole]
,DP.type_desc as [UserType]
,’Exec sp_addrolemember ”’+ user_name(DRM.role_principal_id)+ ”’,’ + ”” + user_name(DRM.member_principal_id)+”” [Command To Add Role Members]
from sys.database_role_members DRM
inner join sys.database_principals DP on DRM.member_principal_id=DP.principal_id
where DRM.member_principal_id>1
Order by DatabaseUser

— Extract Individual Object Permissions
select state_desc + ‘ ‘ + permission_name + ‘ ON [‘ + SCHEMA_NAME(SO.schema_id) + ‘].
[‘+OBJECT_NAME(DP.major_id)
+’] TO [‘ + USER_NAME(DP.grantee_principal_id) + ‘]’ [Command to add Special Permissions]
from sys.database_permissions DP
INNER JOIN sys.database_principals DPS
ON DP.grantee_principal_id=DPS.principal_id
Inner Join sys.objects SO ON SO.object_id=DP.major_id
where DPS.name not in (‘public’,’Guest’)

— Query to find how long a rollback will take to complete
SELECT session_id as SPID, command, start_time, getdate() as CurrentTime,
percent_complete, (estimated_completion_time/1000)/60 as minutes_remaining,
dateadd(second,(estimated_completion_time/1000), getdate()) as estimated_completion_time
FROM sys.dm_exec_requests where percent_complete > 0
–and session_id=158
go

–Run this command to display the port number which SQL Server is listening on.
select local_tcp_port
from sys.dm_exec_connections
where session_id = @@SPID
go

How to bring up a mirror database after a failure of the principal(Prod)

If you have a Witness server then this should happen autmatically, but if there is no witness server then you need bring up the mirror manually with the following command:

ALTER DATABASE dbname SET PARTNER OFF
RESTORE DATABASE dbname WITH RECOVERY

If for some reason you are running with Safety Off (High Performance) then there may be some transactions lost so you have to acknowledge that when restoring the database:

ALTER DATABASE dbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Failing Over

• This is as straight forward as clicking properties on the principal mirrored database, then clicking Mirroring and then click Failover.
• One thing to look out for are databases running in High Performance mode, these can’t be failed over so you will need to convert them to high safety (select high safety then click ok to save) before you fail them over. Later on they can be turned back into high performance.

Set up Mirror Endpoints

Set up Endpoints for Database Mirroring

Create Certificate and end-points (On Principal Instance):

use master;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@55wordxx’;
GO

CREATE CERTIFICATE principal_cert WITH SUBJECT = ‘Principal Server Certificate for database mirroring’;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP (LISTENER_PORT = 51200, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE principal_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

BACKUP CERTIFICATE principal_cert TO FILE = ‘G:\Sqlbackup\Mirroring_certxx_P.cer’
GO

Create Certificate and end-points (On Mirror Instance):

USE master
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘P@55wordxx’;
GO

CREATE CERTIFICATE Mirroring_cert WITH SUBJECT = ‘Mirror Server Certificate for database mirroring’;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED
AS TCP (LISTENER_PORT = 51200, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE Mirroring_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL );
GO

BACKUP CERTIFICATE Mirroring_cert
TO FILE = ‘G:\Sqlbackup\Mirroring_cert_M.cer’
GO

Create User and Associate Certificate (On Principal Instance):

At this stage we need to exchange certificates to other instance. Copy Principal instance certificate on mirror server and Mirror instance certificate to Principal server manually.

USE MASTER
GO

CREATE LOGIN ##MirrorProxy## WITH PASSWORD = ‘P@55wordxx’;
GO

CREATE USER ##MirrorProxy## FOR LOGIN ##MirrorProxy##;
GO

CREATE CERTIFICATE Mirroring_cert
AUTHORIZATION ##MirrorProxy##
FROM FILE = ‘G:\Sqlbackup\Mirroring_cert_M.cer’;
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [##MirrorProxy##];
GO

Create User and Associate Certificate ( On Mirror Instance):
USE MASTER
GO
CREATE LOGIN ##MirrorProxy## WITH PASSWORD = ‘P@55wordxx’;
go

CREATE USER ##MirrorProxy## FROM LOGIN ##MirrorProxy##;
go

CREATE CERTIFICATE principal_cert
AUTHORIZATION ##MirrorProxy##
FROM FILE = ‘G:\Sqlbackup\Mirroring_cert_P.cer’;
Go

GRANT CONNECT ON Endpoint::Endpoint_Mirroring TO [##MirrorProxy##];
go

Also refer to https://tecadmin.net/setup-database-mirroring-sql-server/

Steps to Set up Mirroring

Steps to set up mirroring

Prerequisite: Make sure endpoints are set up

CREATE ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 51200 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
both primary and mirror

1) Alter the log backup job, DBM.LogBackup, to ignore the database temporarily, do this by editing and adding the dbname to @Databases e.g = ‘ALL_DATABASES, -ETWeb11SRBank’

2) Make sure that the database is in full recovery mode and take a full backup and a log backup.

3) Copy the full and log backups over to the mirror server. e.g
FF2SQLETMIR01 (\Ff2sqletmir01\mir)

4) Restore the backup (full and log) with norecovery using following sql, this will set them as mirrored databases;
RESTORE DATABASE [DBSYSSPK]
FROM DISK = ‘G:\Sqlbackup\DBSYSSPK_db_20180804_174400_1_of_1.bak’
WITH NORECOVERY
go
RESTORE LOG [DBSYSSPK]
FROM DISK = ‘G:\Sqlbackup\DBSYSSPK_tlog_20180804_180101.bak’
WITH NORECOVERY
go
ALTER DATABASE [DBSYSSPK]
SET PARTNER = ‘TCP://JKTINDBP0001.id.db.com:51200’
go

5) On the server where you are mirroring from run the folloiwng sql:
ALTER DATABASE [DBSYSSPK]
SET PARTNER = ‘TCP://JKTINDBB0001.ID.DB.COM:51200’
go

6) The final step is to edit job DBM.LogBackup on FF1SQLETW01 and take out the database you added in step (1) e.g “, -ETWeb11SRBank”.

Mirroring Issues

Mirroring issues

If Mirroring stops working between 2 endpoints then there are a few things you can try before you rebuild the mirroring.

One quick thing to try is to stop and start the mirroring endpoint:

select * from sys.endpoints

–To stop an endpoint:

ALTER ENDPOINT dbMirror STATE = STOPPED

–To start an endpoint:

ALTER ENDPOINT dbMirror STATE = STARTED

If all that fails then you will just need to rebuild mirroring by making anote of the mirroring connections, removing it, taking a database dump and tran log dump, copying it over, loading it up and setting up mirroring, For further details refer to “Steps to set up mirroring”

Mirroring Check

–SQL to check status of mirroring:

SELECT
DB_NAME(database_id) As DatabaseName,
CASE WHEN mirroring_guid IS NOT NULL THEN ‘Mirroring is On’ ELSE ‘No mirror configured’ END AS IsMirrorOn,
CASE WHEN mirroring_safety_level=1 THEN ‘High Performance’ WHEN mirroring_safety_level=2 THEN ‘High Safety’ ELSE NULL END AS MirrorSafety,
mirroring_state_desc,
mirroring_role_desc,
mirroring_partner_name AS MirrorServer,
CASE WHEN mirroring_witness_name IS NOT NULL THEN mirroring_witness_name ELSE ‘NULL’ END AS [WitnessServer],
mirroring_witness_state_desc
FROM sys.database_mirroring
where mirroring_guid IS NOT NULL
GO

To copy over the Password for a login do the folling:

• On the source instance run:
  SELECT convert(varbinary(256),password) as password FROM master..syslogins
  WHERE name = ;
• On the destination instance run:
  CREATE LOGIN WITH PASSWORD = HASHED;

To remove users you may need to remove old schemas first and you then need to first check that no objects belong to them:

• First just try to drop the user normally
• You may well get an error saying the user owns objects or schemas in the database
• To check what schema the user owns run:
  SELECT name FROM sys.schemas WHERE principal_id = USER_ID(”);
• Next check which objects are attached to this schema with:
  SELECT *
  FROM sys.objects
  WHERE schema_id = SCHEMA_ID(”);
• You will now be able to drop the schema and then th euser form the database

To realign sysusers with their logins run:

 EXEC sp_change_users_login 'update_one', 'WFMUser', 'WFMUser';

Script for identifying which database and objects are consuming memory on a SQL Server:

–find out how big buffer pool is and determine percentage used by each database

DECLARE @total_buffer INT;
SELECT @total_buffer = cntr_value FROM sys.dm_os_performance_counters
WHERE RTRIM([object_name]) LIKE ‘%Buffer Manager’ AND counter_name = ‘Total Pages’;
;WITH src AS( SELECT database_id, db_buffer_pages = COUNT_BIG(*)
FROM sys.dm_os_buffer_descriptors –WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id)SELECT [db_name] = CASE [database_id] WHEN 32767 THEN ‘Resource DB’ ELSE DB_NAME([database_id]) END, db_buffer_pages, db_buffer_MB = db_buffer_pages / 128, db_buffer_percent = CONVERT(DECIMAL(6,3), db_buffer_pages * 100.0 / @total_buffer)
FROM src
ORDER BY db_buffer_MB DESC;

–then drill down into memory used by objects in database of your choice

USE ;

WITH src AS( SELECT [Object] = o.name, [Type] = o.type_desc, [Index] = COALESCE(i.name, ”), [Index_Type] = i.type_desc, p.[object_id], p.index_id, au.allocation_unit_id
FROM sys.partitions AS p INNER JOIN sys.allocation_units AS au ON p.hobt_id = au.container_id INNER JOIN sys.objects AS o ON p.[object_id] = o.[object_id] INNER JOIN sys.indexes AS i ON o.[object_id] = i.[object_id] AND p.index_id = i.index_id WHERE au.[type] IN (1,2,3) AND o.is_ms_shipped = 0)
SELECT src.[Object], src.[Type], src.[Index], src.Index_Type, buffer_pages = COUNT_BIG(b.page_id), buffer_mb = COUNT_BIG(b.page_id) / 128
FROM src
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON src.allocation_unit_id = b.allocation_unit_id
WHERE b.database_id = DB_ID()
GROUP BY src.[Object], src.[Type], src.[Index], src.Index_Type
ORDER BY buffer_pages DESC;

— SQLto determine Database role permissions granted

SELECT
USER_NAME(grantee_principal_id) AS ‘User’
, state_desc AS ‘Permission’
, permission_name AS ‘Action’
, CASE class
WHEN 0 THEN ‘Database::’ + DB_NAME()
WHEN 1 THEN OBJECT_NAME(major_id)
WHEN 3 THEN ‘Schema::’ + SCHEMA_NAME(major_id) END AS ‘Securable’
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3)
AND minor_id = 0
order by User;

— The Database securable refers to the whole database, so a select there woudl give select permissions to all objects etc.

select d.name, x1 =
(select X1= max(bb.xx)
from (
select xx = max(last_user_seek)
where max(last_user_seek) is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan) is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx = max(last_user_update)
where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join
sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
group by d.name

SQL Server Connection Encryption – Inhouse Operations – Confluence

Prerequisites

 A SSL Certificate is needed to enable the connection encryption.

The following prerequisites must fulfill to setup the connection encryption:

• The app team requests the certificate on dbPKI with the FQDN of the server as Subject without any sub- or prefix, i.e. (fradbhdimu1.de.db.com:65953-1.p12) would not work.
• The certificate must include the public and private key (*.P12  file) and protected with a password.
• Password provided to the implementer.

To setup the certificate it must import into the Windows certificate store first and permissions to the SQL Server Service Account must setup.

1. Open up Management Console (mmc.exe) and add the Certificates Snap-In to the Computer Account – Local computer (use File/Add or Remove Snap-ins):
   
   
2. Import the extracted certificates (both .crt and .p12 ,you’ll need a password for the p12 one) into the personal folder(click on the personal or certificate folder and then right click in the middle window – All Tasks – Import) :
    
   
3. Open “Manged Private Keys” on the .p12 certificate (see below) to grant permission on the certificate to the SQL Server Service Account:
   
   
4. Add the SQL Server Service Account (normally NT Service\MSSQLSERVER) and grant read permission:
   
   
5. Start the SQL Server Configuration Manager and open the Properties of the Network Configuration:
   
   
6. Enable Fore Encryption:
   
   
7. Select the Certificate from the list:
   
8. Restart the SQL Server Service

A quick test to see if its working is to use SSMS to connect and in the options enable “Encrypt Connection”

Troubleshooting

If you have a server with a misconfigured certificate which won’t start up then you can follow these steps to try and fix it and bring it up again:

1) in SQL Configuration Manager turn off “Force Encryption”

2) In Regedit go to key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib and remove the value for Certificate

3) You can now try to restart the SQL Server, if it works great if not then one final step to try is to restore the master database from a recent working backup to another instance of the same version (as oldmasterdb) and then detach it and replace the existing master.mdf and mastlog.ldf files with these restored files.

If you have a situation where you have followed all the steps but can’t see the certificate in the drop down list (7) then you can try these steps:

1) Get the value for the certificate by opening the certificate and looking at details-Thumbprint

2) Convert this thumbprint value to uppercase and remove all spaces

3) Open up Regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib and enter the converted value from above in the Certificate field

4) Final step is to set Force Encryption to Yes and then restart the SQL Server

5) To see if its worked check the SQL Server Error log for the following string: “The certificate [Cert Hash(sha1) “ED806573B335D5DAB8C6037278F5CF6AC4C06804″] was successfully loaded for encryption.”

• Install the Oracle client software by running D:\Install-Staging\Oracle-Client-sw-12102\000\DB_106896.msi
• Copy the tnsnames.ora from D:\Install-Staging\Oracle-Client-sw-12102 to C:\APPS\ORACLE\PRODUCT\12.1.0\CLIENT_64\network\admin
• Restart the SQL Server
• Under Server Objects-Linked Servers Expand Providers select properties for OraOLEDB.Oracle and tick Allow inprocess.
• In SQL Server run the following:
  sp_addlinkedserver @server = ‘DBIBUxx’, @srvproduct = ‘Oracle’, @provider = ‘OraOLEDB.Oracle’, @datasrc = ‘DBIBUxx’
  go
  EXEC sp_addlinkedsrvlogin ‘DBIBUxx’, ‘FALSE’, NULL, ‘DBIBxx_026_USER’, ‘passxxxx’
  go
• That should be it, you can verify by selecting Test connection or by expanding catalogs and viewing the tables and doing a select from them.