Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Mix & Match – Day 4

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.