2012年3月19日星期一

disconnect users from a database

I need to be able to drop all user from one database in script...can someone point me in the right direction or if its really simple, tell me how?
you have to select the spid 's from sysprocesses table that related to db you want and then run kill (spid) command for that spid's.
I think there are some sample scripts to do that on the web like www.databasejornal.com etc..
"Jamie Elliott" wrote:

> I need to be able to drop all user from one database in script...can someone point me in the right direction or if its really simple, tell me how?
|||Hi,
Did you meant to drop the users or disconnect all users from database.
Disconnenct all users in a database:-
alter database <dbname> set single_user with rollback immediate
Drop the users:-
sp_dropuser <user_name>
You could write a script based on sysusers table.
select 'sp_dropuser ' +name +char(10)+'go' from sysusers where uid>4
Execute the below script with text result and copy the result and paste in a
new window to drop all users apart from system created users.
The drop user will fail if the user owns any object. In that case you have
to change the object owner using sp_changeobjectowner system procedure
(See books online)
Thanks
Hari
MCDBA
"Jamie Elliott" <JamieElliott@.discussions.microsoft.com> wrote in message
news:126F63B8-31B4-4F21-9903-6489C4F862F3@.microsoft.com...
> I need to be able to drop all user from one database in script...can
someone point me in the right direction or if its really simple, tell me
how?

没有评论:

发表评论