Posts

Showing posts from January, 2010

sp_change_users_login; Orphan Users

While we are restoring database from prdouction server to our local server there may be some orphaned users in the database.So we can findout those users by running the below query. Use DatabaseName go sp_change_users_login 'report' You will get UserNames and their uniqueidentifier numbers.Then you can update the orphaned users. sp_change_users_login 'update_one','LoginName','loginName' Eg:sp_change_users_login 'update_one','ramesh','ramesh' use DatabaseNameHere GO EXEC sp_change_users_login 'report' go --Pass the values that comes as users from above 'report' list exec sp_change_users_login 'update_one' , 'UserName' , 'UserName' The below commands from dbatools cls Get-DbaDbRestoreHistory ` -SqlInstance SQLServerInstanceName ` -Database DatabaseName ` -Since '2022-10-17 00:00:00' ` -Verbose Get-DbaDbOrphanUser ` -SqlInstance SQLServerInstanceName ` -Database Databas...

Create table with string identity column

CREATE TABLE Alpha ( ID int IDENTITY(0,1), AlphaID as 'PY'+RIGHT('00'+CONVERT(VARCHAR,ID),3)PERSISTED PRIMARY KEY, [DESC] VARCHAR(64) DEFAULT(''), ModifiedDate DATETIME DEFAULT(GETDATE()) ) INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES INSERT Alpha DEFAULT VALUES SELECT * FROM Alpha

When the Procedure Last Executed

SELECT a.execution_count , OBJECT_NAME(objectid) Name, query_text = SUBSTRING( b.text, a.statement_start_offset/2, ( CASE WHEN a.statement_end_offset = -1 THEN len(convert(nvarchar(max), b.text)) * 2 ELSE a.statement_end_offset END - a.statement_start_offset)/2 ) , b.dbid , dbname = db_name(b.dbid) , b.objectid , a.creation_time, a.last_execution_time, a.* FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b WHERE OBJECT_NAME(objectid) = 'ProcedureNameHere' ORDER BY a.last_execution_time DESC

TWEAKING SQLSERVER: Installing SQL Server 2008 On Window server 2008

TWEAKING SQLSERVER: Installing SQL Server 2008 On Window server 2008 : "http://www.windows-noob.com/forums/index.php?/topic/486-how-can-i-install-sql-server-2008-in-windows-server-2008/"

TWEAKING SQLSERVER: Installing SQL Server 2008 On Window server 2008

TWEAKING SQLSERVER: Installing SQL Server 2008 On Window server 2008

TWEAKING SQLSERVER: POINT IN TIME RECOVERY

TWEAKING SQLSERVER: POINT IN TIME RECOVERY

Installing SQL Server 2008 On Window server 2008

http://www.windows-noob.com/forums/index.php?/topic/486-how-can-i-install-sql-server-2008-in-windows-server-2008/