I’ve been scouring my notes (old & new), wading through all my previous research and unearthing advice, tips & tricks that have all helped me in some way or another.
In this series of blog posts, I hope to share a mixture of them with you – some may be simple things you’ve heard read about a thousand times but the hope is that some of you out there, find something in this series they hadn’t heard about or possibly had even forgotten about !
Checking & fixing Orphaned users
To view if there are any orphaned users, run this query against the database; sp_change_users_login ‘report’
To fix;sp_change_users_login ‘update_one’, ‘<databaseUser>‘, ‘<sql login>‘
Dropping all active connections from your database
Use Master
Go
Declare @dbname sysname
Set @dbname = 'name of database you want to drop all connections from'
Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
Execute ('Kill ' + @spid)
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname) and spid > @spid
End
