Thursday, March 22, 2012

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

No comments:

Post a Comment