Fix User Server Principal authentication error after db restore

use database_name;
— ALTER DATABASE database_name SET READ_WRITE

——– Reset SQL user account guids ———————
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = su.name
FROM sysusers su
JOIN sys.server_principals sp ON sp.name = su.name
WHERE issqluser = 1 AND
(su.sid IS NOT NULL AND su.sid <> 0x0) AND
suser_sname(su.sid) is null
ORDER BY su.name

OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE (@@fetch_status = 0)
BEGIN
–PRINT @UserName + ‘ user name being resynced’
exec sp_change_users_login ‘Update_one’, @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END

CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur

Getting Started with .Net MVC c#

Using DB First Approach

Database Part MSSQL Management Studio

  • Create DB Tables,
  • Views and
  • Stored Procedure as requirement

Visual Studio Application Part

  • Create New Project
  • Create Separate Projects for DAL
  • Add Reference of DAL Project to main project
  • Add Nuget package of Entity Framework to both projects [DAL and Main]
  • Create EDMX Model from Database to project in DAL Project
  • Build Project
  • Add scaffolding Controllers of simple tables
  • Test Application

Using Code First Approach

Visual Studio Application Part

  • Create New Project
  • Create Separate Projects for DAL
  • Add Reference of DAL Project to main project
  • Add Nuget package of Entity Framework to both projects [DAL and Main]
  • Create Models and DB Context required for matching database structure
  • Build Project
  • Add scaffolding Controllers
  • Test Application

Common Try outs

  • Analysis of Bind Values, Post Variable capture
  • Necessary change implementation of code for edit method of scaffolding for adapting changes related to hidden and readonly values
  • Add Scaffolding Controllers of Complex tables which has relation/related data with another table and should show its selection in dropdown
  • Know difference between Sync and Async Methods
  • Try Master/Detail Form data entry/edit pages
  • Try Form with Checkboxes and use some nice toggle buttons for checkboxes
  • Use EditorTemplates for Create/Edit Page
  • Use DisplayTemplates for Detail/Delete Page
  • Use of TempData for message display/variable sharing between pages
  • Use of Sessions for Tracking user logins and browsing sessions
  • Use of Partial views for AJAX page result
  • use of JSON Result format for better AJAX Result
  • Use of View-Models for display of data in combined/complex page designs
  • Use of Auto-Mappers for conversion between Domain Models -> View Models and vice-versa

UI Additional Tryouts

Use of bootstrap Component like

TABS, Navs, Chosen [with ajax search], multi select-chosen, CARDS, Panels, Tooltips, Modal, fa icons, collapsible panel, accordions, date-picker, time-picker, tags

Additional Tryouts

  • Separation of DAL project with UI Project
  • Addition of BAL project in solution for application logic
  • Addition of DAL Metadata for validation and label changes
    • Direct in class Indirect using partial class which will be un-affacted in case of db-first model updates
  • Use UIHint to change display of boolean values types to nice Yes/No label in display format and toggle button in editor format
  • Customization of db query with linq for server side filtration and querying for data with criteria
  • Grouping of List data with certain members that can be helpful for report generation
  • Use of Dictionary Data type for quick access of key value data types
  • Use of Dependency Injection for Repo/Service class initialization
  • Use of Repo Class to follow repository pattern
  • Use of Service Class in BAL project to add Business Logic Layer
  • Use of try/catch and Unit or Work Pattern
  • Use of Error Logging frameworks for tracking errors of applications
  • Use of Generic Repo and Generic Services for quick application development process.

Our Basic Classes Intro

  • Use of ViewHelper for formatting number/currencies
  • Use of Flashbag for quick support of TempData
  • Use of NepaliCalendar for General Date conversion
  • Use of RestSharp Nuget Package for quick API consumption

JS Charts

  • d3 charts [pie, line, etc]

TSQL Clear All Database objects

use dbname;

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = ‘ALTER TABLE [‘ + tc2.TABLE_NAME + ‘] DROP [‘ + rc1.CONSTRAINT_NAME + ‘]’
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec sp_executesql @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END

CLOSE @Cursor DEALLOCATE @Cursor
GO

EXEC sp_MSforeachtable ‘DROP TABLE ?’
GO

MSSQL Check Running Queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

TSQL Min, Max, AVG and Last Value in single query

declare @MyTable table
(
SN INT
,[Month] INT
,Acc INT
,Bal INT
)
— GO

INSERT INTO @MyTable VALUES
(1, 7, 101, 1000),
(2, 7, 101, 800),
(3, 7, 101, 1700),
(4, 8, 101, 1200),
(5, 8, 101, 900),
(6, 9, 101, 2500)
— GO

select distinct Acc, [Month], MinBal, AvgBal, MaxBal, LastVal from
(
SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM @MyTable
) y
–group by Acc, [Month], MinBal, AvgBal, MaxBal, LastVal

SELECT Acc,[Month],MAX(MinBal)MinBal,MAX(AvgBal)AvgBal,MAX(MaxBal)MaxBal,MAX(lastVal)lastVal
FROM
(
SELECT sn,Acc,[Month] ,Bal
, MIN(Bal) OVER(PARTITION BY Acc,[Month]) MinBal
, AVG(Bal*1.) OVER(PARTITION BY Acc,[Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc,[Month]) MaxBal
, FIRST_VALUE(Bal) OVER(PARTITION BY Acc,[Month] ORDER BY SN DESC) lastVal
FROM @MyTable
)u GROUP BY Acc,[Month]

 

Sweet one solutions suggested:

select *
from
( SELECT sn, Acc, [Month], Bal
, MIN(Bal) OVER(PARTITION BY Acc, [Month]) MinBal
, AVG(Bal) OVER(PARTITION BY Acc, [Month]) AvgBal
, MAX(Bal) OVER(PARTITION BY Acc, [Month]) MaxBal
, row_number() OVER(PARTITION BY Acc, [Month] ORDER BY SN DESC) as rn
from @MyTable
) tt
where rn = 1
ORDER By sn

Some useful Commands for Check and Maintain MSSQL Database

— Shrink Database
DBCC SHRINKDATABASE (AdventureWorks2008R2, TRUNCATEONLY);

— Rebuild All Index on a table
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);

— Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee REORGANIZE ;

— Check DB for database consistency
DBCC CHECKDB

— Reclaims space from dropped variable-length columns in tables or indexed views
DBCC CLEANTABLE

— Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.
DBCC UPDATEUSAGE (AdventureWorks2012)