Wednesday 26 March 2014

How to restore orphaned users in TSQL after database restore

Use the following script

 

USE [master]

SET NOCOUNT ON;

-- Create a temp table to hold orphaned accounts

if OBJECT_ID('tempdb..#Orphans') is not NULL DROP TABLE #Orphans

CREATE TABLE #Orphans (

      ID                INT NOT NULL IDENTITY,

      DBName            NVARCHAR(MAX) DEFAULT NULL,

      Username    NVARCHAR(MAX),

      userSID           NVARCHAR(MAX)

)

-- For each database get orphaned accounts

EXEC sp_MSforeachdb 'use [?]; insert into #Orphans (Username,UserSID) EXEC sp_change_users_login ''REPORT''; update #Orphans SET dbname = ''?'' where dbname is NULL'

-- Remove DBs that are not ONLINE or that are READ_ONLY as we can't alter them.

DELETE FROM #Orphans WHERE NOT [DBName] IN

(

SELECT name

FROM sys.databases

WHERE state_desc = 'ONLINE' AND is_read_only = 0 -- Databases that are ONLINE and NOT Read_Only

)

-- Loop through all remaining orphans and attempt to un-orphan them

DECLARE @CurRow   INT = 1

DECLARE @EndRow INT = (SELECT MAX(ID) FROM #Orphans)

DECLARE @SQLCmd   NVARCHAR(MAX)

WHILE @CurRow <= @EndRow

BEGIN

SET @SQLCmd = (SELECT 'USE [' + DBName + ']; EXEC sp_change_users_login ''UPDATE_ONE'', ''' + Username + ''', ''' + username + ''';' FROM #Orphans WHERE ID = @CurRow)

SET @CurRow = @CurRow + 1

PRINT @SQLCmd

EXECUTE (@SQLCmd) -- re associate the login with the user account

END

-- Drop temp table holding orphaned accounts

if OBJECT_ID('tempdb..#Orphans') is not NULL DROP TABLE #Orphans

Wednesday 12 March 2014

How to run a SQL Query across multiple databases with one query

In SQL Server management studio, using, View, Registered Servers (Ctrl+Alt+G) set up the servers that you want to execute the same query across all servers for, right click the group, select new query. Then when you execute the query, the results will come back with the first column showing you the database instance that that row came from.

image

Monday 10 March 2014

Wednesday 5 March 2014

How to name the tabs in an SSRS report exported to Excel

Quite simply, use the PageName property.

For more information on how to do this using expressions for each page group, see here...

http://blogs.msdn.com/b/robertbruckner/archive/2010/05/16/report-design-naming-excel-worksheets.aspx

How to tell if a machine is a virtual machine in Windows

Two methods

1. Start, Run..., cmd.exe, systeminfo.exe, then search for system manufacturer, and if virtual it will be listed as follows :-

If this is a VMware virtual machine, result should be:

System Manufacturer: VMware, Inc.
System Model: VMware Virtual Platform

If this is a Hyper V or a Microsoft Virtual PC machine the result should be like this:

System Manufacturer: Microsoft Corporation
System Model: Virtual Machine

2. Start, Run..., msinfo32.exe - basically a graphical user interface showing much the same information as you get from method 1.

image

How to find the last interactive logons in Windows using PowerShell

Use the following powershell script to find the last users to login to a box since a given date, in this case the 21st April 2022 at 12pm un...