drop users connected to a database

hi there,
any sql code example out there to do this?
thanks,
PaulLookup KILL command in the BOL
"Milsnips" <milsnips@.hotmail.com> wrote in message
news:uZg$LL$%23FHA.3636@.TK2MSFTNGP10.phx.gbl...
> hi there,
> any sql code example out there to do this?
> thanks,
> Paul
>|||Do you want to drop logins (i.e. prevent the users from accessing the server
)?
Do you want to deny the users access to a specific database?
Do you want to disconnect user sessions?
Which is it? What version of SQL Server are you using?
Look up in Books Online:
1) sp_droplogin to drop logins in SQL 2000, or the DROP LOGIN statement for
SQL 2005;
2) sp_revokedbaccess to deny access to a SQL 2000 database, or the DENY
statement for SQL 2005;
3) kill to disconnect individual processes (identify them first with sp_who
or sp_who2).
ML
http://milambda.blogspot.com/|||alter database <dbname> set single_user with rollback immediate
The rollback immediate option will automatically terminate all sessions and
rollback any active transactions, so it's basically the nuclear option. Hee
Heee ;-)
http://msdn.microsoft.com/library/d...>
_03_725v.asp
"Milsnips" <milsnips@.hotmail.com> wrote in message
news:uZg$LL$%23FHA.3636@.TK2MSFTNGP10.phx.gbl...
> hi there,
> any sql code example out there to do this?
> thanks,
> Paul
>|||Don=B4t know why you want to drop them, but if you want to do something
adminstrative could go by this:
ALTER DATABASE <Nameofthedb> SET
SINGLE_USER with rollback immediate
<DoSomethingAdministrative>
ALTER DATABASE <Nameofthedb> SET=20
MULTI_USER=20
HTH, Jens Suessmeyer.|||I'd check if any of the users carry guns before trying that. :)
ML
http://milambda.blogspot.com/|||Well, I send out a group wide email notification ahead of time. If it's past
5:00pm, most users are looking for an excuse to just pack up and go home
anyway. ;-)
"ML" <ML@.discussions.microsoft.com> wrote in message
news:22239C1E-F70B-4D46-9A1D-C72D342A1502@.microsoft.com...
> I'd check if any of the users carry guns before trying that. :)
>
> ML
> --
> http://milambda.blogspot.com/|||I don't think he wants to actually drop their user id from the database;
just kill the connection.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1134048213.022697.236960@.z14g2000cwz.googlegroups.com...
Dont know why you want to drop them, but if you want to do something
adminstrative could go by this:
ALTER DATABASE <Nameofthedb> SET
SINGLE_USER with rollback immediate
<DoSomethingAdministrative>
ALTER DATABASE <Nameofthedb> SET
MULTI_USER
HTH, Jens Suessmeyer.|||Actually the rollback normal with time-out would probably be more
appropriate, if you can give them 1/2 hour to wrap things up.
"JT" <someone@.microsoft.com> wrote in message
news:%23SVlYl$%23FHA.3632@.TK2MSFTNGP10.phx.gbl...
> alter database <dbname> set single_user with rollback immediate
> The rollback immediate option will automatically terminate all sessions
> and rollback any active transactions, so it's basically the nuclear
> option. Hee Heee ;-)
> http://msdn.microsoft.com/library/d...
es_03_725v.asp
>
> "Milsnips" <milsnips@.hotmail.com> wrote in message
> news:uZg$LL$%23FHA.3636@.TK2MSFTNGP10.phx.gbl...
>|||Well, I guess it's about that time wherever the OP is. :)
ML
http://milambda.blogspot.com/sql

Drop temporary tables whilst connected ?

Just a quicky about temporarary tables. If using QA, when you create a
temporary table, it gets dropped if you close the query. Otherwise you
need to state 'DROP TABLE myTable' so that you can re-run the query
without the table being there.

Sometimes, you can have quite lengthy SQL statements (in a series)
with various drop table sections throughout the query. Ideally you
would put these all at the end, but sometimes you will need to drop
some part way through (for ease of reading and max temp tables etc...)

However, what I was wondering is :

Is there any way to quickly drop the temporary tables for the current
connection without specifying all of the tables individually ? When
testing/checking, you have to work your way through and run each drop
table section individually. This can be time consuming, so being
naturally lazy, is there a quick way of doing this ? When working
through the SQL, it's possible to do this quite a lot.

Example

SQL Statement with several parts, each uses a series of temporary
tables to create a result set. At the end of a section, these work
tables are no longer needed, so drop table commands are used. The
final result set brings back the combined results from each section
and then drops those at the end.

TIA

RyanRyan (ryanofford@.hotmail.com) writes:
> Just a quicky about temporarary tables. If using QA, when you create a
> temporary table, it gets dropped if you close the query. Otherwise you
> need to state 'DROP TABLE myTable' so that you can re-run the query
> without the table being there.
> Sometimes, you can have quite lengthy SQL statements (in a series)
> with various drop table sections throughout the query. Ideally you
> would put these all at the end, but sometimes you will need to drop
> some part way through (for ease of reading and max temp tables etc...)
> However, what I was wondering is :
> Is there any way to quickly drop the temporary tables for the current
> connection without specifying all of the tables individually ? When
> testing/checking, you have to work your way through and run each drop
> table section individually. This can be time consuming, so being
> naturally lazy, is there a quick way of doing this ? When working
> through the SQL, it's possible to do this quite a lot.

No, there is no "DROP TABLE #%".

You could write a cursor over tempdb..sysobjects which finds the tables,
but then you would have to mask out the part which is tacked on to the
table name. Kind of messy.

On the other hand, why not pack everything in a stored procedure? A temp
created in a scope is dropped when that scope exits. Thus, with a stored
procedure, this is a non-problem.

If using a stored procedure is problematic for some reason, a RAISERROR
with level 21 is a brutal way if getting rid of the temp tables - in
fact, this kills your connection. Only do this, if you are your own DBA,
because it may ping an alert for an operator on a big server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> On the other hand, why not pack everything in a stored procedure? A temp
> created in a scope is dropped when that scope exits. Thus, with a stored
> procedure, this is a non-problem.

Most of this type of query will be put into a stored procedure once
finished, but we most often need to work through it in stages to check
that we have the maths correct at each stage before we progress this
further into an SP. We do a lot of manipulating financials so need to
check our maths throughout. We have a lot of reports based on our
figures and each needs to use the same logic but slightly different
groups of answers which needs checking.

In a lot of cases the SQL can be over a thousand lines long, so we
tend to break it down as much as possible in order to keep it simple.
Hence grouping the drop table statements so we can work with it.

Thanks

Ryan

drop 1 article from publication and add it back, sql server 2000 s

Recently I have experienced problems with subscriptions getting out of sync.
Some of the published tables are very large and are connected to the
subscriber via t1 connection. To avoid re-initializing the entire publication
I ran sp_dropsubscription followed by sp_droparticle, both specifying the
article that is out of sync. Then I opened the publication properties from
the replication monitor, went to the articles tab, and added the article back
to the publication. When I run the snapshot again a new snapshot is generated
for that article and it subsequently is pushed to the subscriber. Sometimes
the artilces will get out of sync again in a day or two and some articles
stay synchronized without any problems.
The script I run to drop the article is:
use db1
go
declare @.p varchar(128)
declare @.a varchar(128)
declare @.db varchar(128)
set @.p = 'db1_Publication'
set @.a = 'tbl1'
set @.db = 'db1'
exec sp_dropsubscription @.publication = @.p ,@.article = @.a
,@.subscriber = 'rptServer' ,@.destination_db = @.db
exec sp_droparticle @.publication = @.p
,@.article= @.a
,@.force_invalidate_snapshot = 0
I believe this is the correct way to drop 1 article from a publication that
contains many articles and adding it back again to reinitialize, but I
haven't been able to find any examples of this anywhere.
The question I pose to the replication gurus is, does this work? I am unsure
at this point due to the inconsistent results I've been getting.
Thanks,
Jason
Jason,
this is much the same way I do it as well (see
http://www.replicationanswers.com/AddColumn.asp). I would like to
investigate the non-synchronization of these tables though. Do you see any
commands in the distribution database for these tables (sp_browsereplcmds)?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)