Monday, 7 April 2014

Ninject cheat sheet

A little cheat sheet for how to do things with Ninject that made implementing super-simple:

http://lukewickstead.wordpress.com/2013/01/18/ninject-cheat-sheet/

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

Monday, 17 February 2014

How to query Active Directory from SQL server

SET UP a Linked server with name ADSI to make this work (shown below)

clip_image002

clip_image002[4]

clip_image002[6]

Also further on you'll see the use of the userAccountControl field, which is a flags field that stores user account details. The list of which flags are which can be found here...

http://msdn.microsoft.com/en-us/library/windows/desktop/ms680832(v=vs.85).aspx

 

use [utilities]
SET NOCOUNT ON;

-- Ensure database link to AD called ADSI exists...

IF object_id('[Utilities].[dbo].[AccountSyncCheck]') IS NOT NULL DROP TABLE [dbo].[AccountSyncCheck]
CREATE TABLE [dbo].[AccountSyncCheck](
      [LOGINID] [varchar](100) collate database_default NOT NULL,
      [EMAILADDRESS] [varchar](255) collate database_default NULL,
      [EMPLOYEEID] [varchar](10) collate database_default NULL,
      [FIRSTNAME] [varchar](255) collate database_default NULL,
      [LASTNAME] [varchar](255) collate database_default NULL,
      [DEPARTMENT] [varchar](255) collate database_default NULL,
      [OFFICENAME] [varchar](255) collate database_default NULL,
      [DISPLAYNAME] [varchar](255) collate database_default NULL,
      userAccountControl      int,
CONSTRAINT [PK_AccountSyncCheck_LOGINID] PRIMARY KEY CLUSTERED
(
      [LOGINID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

IF object_id('tempdb..#temp') IS NOT NULL  DROP TABLE #temp;
CREATE TABLE #Temp
(
    samaccountname             VARCHAR (100) ,
    givenname                  VARCHAR (255)  NULL,
    sn                         VARCHAR (255)  NULL,
    mail                       VARCHAR (255)  NULL,
    department                 VARCHAR (255)  NULL,
    physicalDeliveryOfficeName NVARCHAR (255) NULL,
    employeeid                 VARCHAR (15)   NULL,
    displayname                VARCHAR (255)  NULL,
    userAccountControl              int null
);


DECLARE @sql      AS VARCHAR (MAX)
DECLARE @i        AS INT = ASCII('A');

WHILE @i <= ASCII('Z')
    BEGIN
        SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                    FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                                          FROM ''''LDAP://dc=mydomain,dc=com'''' 
                            WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                            AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                            AND         samaccountname >= '''' ' + char(@i) + '  ''''  and  samaccountname < '''' ' + char(@i + 1) + ' ''''  
                            '') ';
PRINT @SQL
        INSERT INTO #Temp
        EXECUTE (@sql);
        SET @i = @i + 1;
    END

-- deal with oddball characters
SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname ,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname < ''''A''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

SET @sql = 'SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM OPENQUERY( ADSI,
                        ''SELECT samaccountname, givenname, sn, mail, department, physicalDeliveryOfficeName,employeeid,displayname,userAccountControl
                        FROM ''''LDAP://dc=mydomain,dc=com'''' 
                        WHERE objectCategory = ''''Person'''' AND objectClass= ''''user'''' 
                        AND         userprincipalname = ''''*'''' AND mail = ''''*'''' AND SN = ''''*'''' 
                        AND         samaccountname >= ''''ZZZZZZZ''''
                        '') ';

INSERT INTO #Temp
EXECUTE (@sql);

IF (SELECT COUNT(*)
    FROM   #Temp) > 0
    BEGIN
        truncate table AccountSyncCheck
        INSERT INTO AccountSyncCheck (LOGINID,EMAILADDRESS,EMPLOYEEID,FIRSTNAME,LASTNAME,DEPARTMENT,OFFICENAME,DISPLAYNAME, userAccountControl)
        SELECT   samaccountname,
                 mail,
                 LEFT(employeeid, 10),
                 givenname,
                 sn,
                 department,
                 physicalDeliveryOfficeName,
                 displayname,
                 userAccountControl
        FROM     #Temp
        ORDER BY samaccountname;
    END

SET NOCOUNT OFF;


--select top 10 * from beacon.dbo.rraperson
select *
from utilities.dbo.AccountSyncCheck

/*
SELECT     
'active RRA person(s) with invalid email address' AS     alert,
iv.PersonID, InternetAddress, Preferred, OfficeID, Loginname, Active, Responsibility, RetiredDate
from beacon.dbo.IV_PersonInternetAddressCompletePreferred iv
inner join beacon.dbo.rraperson r on r.personid = iv.personid
where ISNULL(iv.internetaddress, '') <> ''
--and iv.internetaddress not like '%@mydomain.com%' and iv.internetaddress not like '%@russreyn.com%'
and active = 1
and iv.personid not in (10000165,40002053,900008913,40001263,40001285)
*/

-- Active in Beacon disabled in AD
select *
from utilities.dbo.AccountSyncCheck

-- find active where they should be inactive
select *
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydomain\' + b.loginid 
where r.Active = 1
and useraccountcontrol & 2 = 2


-- find beacon spelling mistakes in email addresses
select b.loginid, pcp.PersonID, ActiveDirectoryEmailAddress = b.emailaddress, BeaconEmailAddress = pcp.InternetAddress
from AccountSyncCheck b
inner join beacon.dbo.rrapersonsummary r on r.loginname = 'mydonamin\' + b.loginid 
inner join Beacon.dbo.IV_PersonInternetAddressCompletePreferred pcp on pcp.PersonID = r.id
where pcp.InternetAddress <> b.emailaddress
and r.Active = 1
and useraccountcontrol & 2 = 0