Set Single User in SQL Server 2008

I would like to have a way to make sure all users are out of a database before a reporting update..,I plan on using Deny Select to keep additional users out while my process runs but I also want a way to "kill" any connections other than my own..I searched on line and found the attached code.. My question is this: When I set single user will that "kill" all other connections? How do I put it back at the end of my process?

ALTER DATABASE [DATABASE_NAME] 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE

Open in new window

LVL 1
cheryl9063Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
No, it will not.  To do that you'll need to cycle through the users and kill the sessions.
chapmandewCommented:
actually, i lied to you...it will kill them.  just confirmed it.
cheryl9063Author Commented:
Will the attached code work?
CREATE PROCEDURE dbo.clearDBUsers 
    @dbName SYSNAME 
AS 
BEGIN 
    SET NOCOUNT ON 
 
    DECLARE @spid INT, 
        @cnt INT, 
        @sql VARCHAR(255) 
 
    SELECT @spid = MIN(spid), @cnt = COUNT(*) 
        FROM master..sysprocesses 
        WHERE dbid = DB_ID(@dbname) 
        AND spid != @@SPID 
 
    PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.' 
     
    WHILE @spid IS NOT NULL 
    BEGIN 
        PRINT 'About to KILL '+RTRIM(@spid)  
        SET @sql = 'KILL '+RTRIM(@spid) 
        EXEC(@sql)  
        SELECT @spid = MIN(spid), @cnt = COUNT(*) 
            FROM master..sysprocesses 
            WHERE dbid = DB_ID(@dbname) 
            AND spid != @@SPID  
        PRINT RTRIM(@cnt)+' processes remain.' 
    END 
END
GO

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

chapmandewCommented:
I am sure it will, but you don't need it.  Your first comment will kill them and roll trans back.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as chapmandev confirmed meanwhile, the:
WITH ROLLBACK IMMEDIATE

will kill all the sessions (except your own...)
cheryl9063Author Commented:
That is much easier.. How do you do the same on just a table?
chapmandewCommented:
huh?  You can't.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree, you cannot have "single" access on a table.
you might want to clarify what you are trying to achieve?
cheryl9063Author Commented:
When MS Access users are pulling data from views they sometimes lock tables that we are trying to update..
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you might want to review the views' design, the table's indexes, or eventually use the (NOLOCK) hint in the views

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chapmandewCommented:
yeah, I would create views that the users query instead of the tables

you can also set the nolock hint in the access query.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.