Need to Disconnect users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Need to Disconnect users

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-02-12 : 09:55:22

Morning,

I am new to SQL 2008 the last time I administered SQL was v.2000! I have been tasked with kicking out all the users, making a copy of the database and only allowing certain users back in. I've got the copy database script ready to go. I found the script spKillUSers and wanted to use that to kick out the users but when I run it against the development database to check...


exec Production.dbo.spKillUsers 'Training1-11-10'

I get the message


Msg 213, Level 16, State 7, Procedure sp_who, Line 79
Column name or number of supplied values does not match table definition.

I made sure I copied the script exactly. Does it have to be in master to run? Also is there a way allow certain machine names back in? I ask because the application for this database logs in as one user. It holds the user accounts in an outside access database...yes I know nothing I can do about that.

Is there an elegant solution to this? One I can schedule to run?

Thanks for any help you can provide

Laura

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:21:45

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
or
ALTER DATABASE MyDatabaseName SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

then

ALTER DATABASE MyDatabaseName SET MULTI_USER

when you are done

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-02-12 : 10:24:25

Thanks Kristen I'll look at those. Thanks again.

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-02-12 : 10:36:12

Quick question: the ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE wont work unless all users are out first right? If so does anyone know if spKillUsers works with 2008?

This is the code I snagged:


create procedure [dbo].[spKillUsers]
@dbName varchar(32)

as
declare @spid smallint
declare @cmd varchar(32)

create table #tmp

(spid smallint,
status varchar(32),
loginame varchar(32),
hostname varchar(32),
blk char(8),
dbname varchar(32),
cmd varchar(255))

declare cLogin cursor for select spid from #tmp where dbname = @dbName 

insert into #tmp exec sp_who

open cLogin
fetch cLogin into @spid

while @@fetch_status = 0
begin
select @cmd = 'kill ' + CONVERT(char, @spid)
print @cmd
execute( @cmd )
fetch cLogin into @spid
end

close cLogin
deallocate cLogin

when I try to run it it


exec Production.dbo.spKillUsers 'Training1-11-10'

It doesn't like it. Any ideas would be great.

Thanks

Laura

Kristen
Test

22859 Posts

Posted - 2010-02-12 : 10:42:38

" ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE wont work unless all users are out first right?"

Nah, the ROLLBACK IMMEDIATE will throw them off

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-02-12 : 10:49:56

Sweet. Thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources