Regain Lost Access to SQL Server 2008 R2

Log on the server hosting the SQL Server as administrator

Stop all  SQL services if SQL services are running.

SQL Server 2008 R2 Configuration Manager
SQL Server 2008 R2 Configuration Manager
SQL Services Stopped

SQL Services Stopped

Start a command prompt as administrator.

Command prompt

Command prompt

Change the directory to SQL Server Bin folder:
In SQL server 2008 r2 the path would be “C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn

Start the SQL Server in single user mode:
Type SQLServr.Exe –m at command prompt

sql server single user mode

Starting SQL server in single user mode

Start another command prompt as administrator

Change directory to SQL server bin folder.

Connect to SQL server as admin
SQLCMD –S <Server_Name\Instance_Name>

SQLCMD

SQLCMD

Create a new login:
CREATE LOGIN <Login_Name> with PASSWORD=’<Password>’
GO

CreateLogin

Create Login

Assign sysadmin role:
SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’

Add sysadmin role

Add sysadmin role

Exit the SQLCMD Command prompt
Type Exit press Enter   (Get out of SQL prompt)
Type Exit press Enter   (Get out of Command prompt)

Exit the sqlservr.exe Command prompt
Type Control C
Do you wish to shutdown SQL Server (y/n)?  –> y
Type Exit press Enter

Start all SQL Services at SQL Configurations Manager

Login using new admin account!

Cheers!

Sanjay

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Allocated Quantities

(excluding Depot management, Project Accounting, Service call & Manufacturing)

select  'Allocated Returns in Purchase Order Processing' as AllocType, A.POPRCTNM as document,A.ITEMNMBR, QTYRESERVED as allocatedqty
from POP10500 A join POP10310 B
on A.POPRCTNM=B.POPRCTNM AND
A.RCPTLNNM=B.RCPTLNNM AND
A.ITEMNMBR=B.ITEMNMBR 
where  A.QTYRESERVED > 0 

union all

select  'Allocated Returns in Purchase Order Processing' as AllocType,A.POPRCTNM as document,A.ITEMNMBR, QTYRESERVED as allocatedqty
 from POP10500 A join POP10310 B 
on B.POPRCTNM=B.POPRCTNM AND
A.RCPTLNNM=B.RCPTLNNM AND A.ITEMNMBR=B.ITEMNMBR 
where  A.QTYRESERVED > 0

union all

select 'Allocated Fulfillment Orders in Sales Order Processing' as AllocType, SOPNUMBE as document,ITEMNMBR,ATYALLOC as allocatedqty 
from SOP10200 
where  ATYALLOC > 0 and  SOPTYPE = 6

union all

select 'Allocated Invoices in Sales Order Processing' as AllocType, SOPNUMBE as document,ITEMNMBR,ATYALLOC as allocatedqty
from SOP10200 
where ATYALLOC > 0 and  SOPTYPE = 3

union all

select	'Allocated Orders in Sales Order Processing' as AllocType ,
		SOPNUMBE as document,
		ITEMNMBR,
		ATYALLOC as allocatedqty
from	SOP10200 
where	ATYALLOC > 0 and  SOPTYPE = 2

union all 

select 'Allocated documents in Inventory' as AllocType ,
IVDOCNBR as Document,
ITEMNMBR,
TRXQTY as AllocatedQTY
from IV10001 where TRXQTY < 0 

union all

select 'Allocated documents in Inventory' as AllocType ,
IVDOCNBR as Documnet,
ITEMNMBR,
TRXQTY as allocatedQTY
from IV10001 where  TRXQTY > 0 and IVDOCTYP = 3

union all 

select 'Allocated Invoices in Invoicing' as AllocType ,
INVCNMBR as Document,
ITEMNMBR,
QUANTITY as allocatedQTY 
from IVC10101 where  DOCTYPE = 1

union all 

select 'Allocated Assembly documents in Bill of Materials' as AllocType, 
TRX_ID as Document,
ITEMNMBR,
ATYALLOC as Allocated 
from BM10300 where Component_ID <> 0 
and  ATYALLOC > 0

Manufacturing Allocated or pending issue


select 'Allocated - general or pending issue' as AllocType,
        CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
            WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
            WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
            WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
            WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
            WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
            WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
            WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
        END AS MOstatus,
 *
from MOP1400 
inner join dbo.WO010032 
ON dbo.MOP1400 .MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
where ATYALLOC > 0 

select 'Allocated - bin ' as AllocType,
        CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
            WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
            WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
            WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
            WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
            WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
            WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
            WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
        END AS MOstatus,
		*
from MOP1900 
inner join dbo.WO010032 
ON dbo.MOP1900.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
where ATYALLOC > 0

select 'Allocated - lot or pending issue' as AllocType,
        CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
            WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
            WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
            WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
            WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
            WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
            WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
            WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
        END AS MOstatus,
        * 
from MOP1020 
inner join dbo.WO010032 
ON dbo.MOP1020.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I

Pending manufacturing transactions that can hold allocations


select	'MOP1025' as AllocType ,
		CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
			WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
			WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
			WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
			WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
			WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
			WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
			WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
		END AS MOstatus,
		* 
FROM         dbo.MOP1025 INNER JOIN
                      dbo.WO010032 ON dbo.MOP1025.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
WHERE     (dbo.MOP1025.MOPDOCNUM IN
                          (SELECT     PICKNUMBER
                            FROM          dbo.MOP1200
                            WHERE      (POSTED = 1)))

select 'MOP1020' as AllocType ,
		CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
			WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
			WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
			WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
			WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
			WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
			WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
			WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
		END AS MOstatus	,
		*	
FROM         dbo.MOP1020 INNER JOIN
                      dbo.WO010032 ON dbo.MOP1020.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
WHERE     (dbo.MOP1020.DOCNUMBR IN
                          (SELECT     PICKNUMBER
                            FROM          dbo.MOP1200
                            WHERE      (POSTED = 1)))
                           

SELECT     'MOP1020' AS AllocType, dbo.MOP1026.MANUFACTUREORDER_I,
			CASE WHEN MANUFACTUREORDERST_I = '1' THEN 'quote'
				WHEN MANUFACTUREORDERST_I = '2' THEN 'open'
				WHEN MANUFACTUREORDERST_I = '3' THEN 'released'
				WHEN MANUFACTUREORDERST_I = '4' THEN 'hold'
				WHEN MANUFACTUREORDERST_I = '5' THEN 'cancelled'
				WHEN MANUFACTUREORDERST_I = '6' THEN 'complete'
				WHEN MANUFACTUREORDERST_I = '7' THEN 'partially rec.'
				WHEN MANUFACTUREORDERST_I = '8' THEN 'closed'
			END AS MOstatus	,
			*
FROM         dbo.MOP1026 INNER JOIN
                      dbo.WO010032 ON dbo.MOP1026.MANUFACTUREORDER_I = dbo.WO010032.MANUFACTUREORDER_I
WHERE     (dbo.MOP1026.MOPDOCNUM IN
                          (SELECT     PICKNUMBER
                            FROM          dbo.MOP1200
                            WHERE      (POSTED = 1)))