disconnect users from a database

I need to be able to drop all user from one database in script...can someon
e 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 yo
u 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.databasejorna
l.com etc..
"Jamie Elliott" wrote:

> I need to be able to drop all user from one database in script...can someone poin
t 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?

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?

Disconnect all connections

Hi All,
I trying to write a script that will detach a DB copy the physical file to a new location then remount the original DB.
the Problem that i am having it detaching the DB as i get error telling me that the DB is in use.
There is an Access front end open with and ODBC connection to the SQL DB and this needed to be open to as it is where the command to create the Copy is coming from.

I have tried the following code
USE master ALTER DATABASE EclipseSQL SET SINGLE_USER with no_wait
exec sp_detach_db 'EclipseSQL'
exec xp_cmdshell 'MKDIR "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices" '

exec xp_cmdshell 'ECHO Y| cacls "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" /p everyone:f'

exec xp_cmdshell 'xCopy "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf" "C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\Archive\06_2007_invoices\" '

exec sp_Attach_db 'EclipseSQL', 'C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Data\EclipseSQL.mdf'

i get the following output from this

Msg 5070, Level 16, State 2, Line 1
Database state cannot be changed while other users are using the database 'EclipseSQL'
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Msg 3703, Level 16, State 2, Line 1
Cannot detach the database 'EclipseSQL' because it is currently in use.

(2 row(s) affected)

(2 row(s) affected)

(3 row(s) affected)
Msg 1801, Level 16, State 3, Line 1
Database 'EclipseSQL' already exists. Choose a different database name.

any know how i get disconnect all users no questions asked?

Try:

Code Snippet

alterdatabase EclipseSQL setread_onlywithrollback immediate

go

alterdatabase EclipseSQL setread_writewithrollback immediate

go

|||That did the trick thanks

Disabling guest account doesn't work as expected (please help!)

Hi all, I want to disable access of the SQL guest account except
master and tempdb. I tried it for one database now, but before I used
this script in query analyzer to check whether the guest account had
access whatsoever:
select has_dbaccess('DatabaseName') as Access,* from sysusers
and it gave me a 1 for the row corresponding to guest:
>1< 2 0 guest 0x00 0x00 2000-08-06 01:27:55.187[...]
now I tried to disable it for the database via:
USE <DatabaseName
GO
EXECUTE sp_revokedbaccess guest
and I got an appropriate message that the account was disabled.
When I queried the sysusers table again as I did in the first
statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
this possible? In addition to this problem I tried to disable it again
for the same database, but now I get "User 'guest' does not exist in
the current database." which is actually that what I expected after
disabling the account, but why does it still show up in sysusers with
access to exactly the database that I disabled its access to!'!?!
Any help greatly appreciated, thank you
TimGuest always exists in sysusers, it's just only enabled when
you add the user. If it's not enabled in the database, the
status is 0 in sysusers.
-Sue
On 6 Apr 2004 09:07:17 -0700, google@.supertimmy.com (Tim
Gorgs) wrote:

>Hi all, I want to disable access of the SQL guest account except
>master and tempdb. I tried it for one database now, but before I used
>this script in query analyzer to check whether the guest account had
>access whatsoever:
>select has_dbaccess('DatabaseName') as Access,* from sysusers
>and it gave me a 1 for the row corresponding to guest:
>now I tried to disable it for the database via:
>USE <DatabaseName
>GO
>EXECUTE sp_revokedbaccess guest
>and I got an appropriate message that the account was disabled.
>When I queried the sysusers table again as I did in the first
>statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
>this possible? In addition to this problem I tried to disable it again
>for the same database, but now I get "User 'guest' does not exist in
>the current database." which is actually that what I expected after
>disabling the account, but why does it still show up in sysusers with
>access to exactly the database that I disabled its access to!'!?!
>Any help greatly appreciated, thank you
>Tim|||> select has_dbaccess('DatabaseName') as Access,* from sysusers
Note that there is no correlation in this query between HAS_DBACCESS and the
sysusers table so HAS_DBACCESS will have the same value for each row
returned. If your login has access to the specified database, it will
return 1 for all rows.
The guest account will always be in the sysusers table but can
enabled/disabled using sp_grantdbaccess/sp_revokedbaccess (or
sp_adduser/sp_dropuser). The guest account is disabled by default in user
databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Gorgs" <google@.supertimmy.com> wrote in message
news:afa954a3.0404060807.509acc2a@.posting.google.com...
> Hi all, I want to disable access of the SQL guest account except
> master and tempdb. I tried it for one database now, but before I used
> this script in query analyzer to check whether the guest account had
> access whatsoever:
> select has_dbaccess('DatabaseName') as Access,* from sysusers
> and it gave me a 1 for the row corresponding to guest:
> now I tried to disable it for the database via:
> USE <DatabaseName
> GO
> EXECUTE sp_revokedbaccess guest
> and I got an appropriate message that the account was disabled.
> When I queried the sysusers table again as I did in the first
> statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
> this possible? In addition to this problem I tried to disable it again
> for the same database, but now I get "User 'guest' does not exist in
> the current database." which is actually that what I expected after
> disabling the account, but why does it still show up in sysusers with
> access to exactly the database that I disabled its access to!'!?!
> Any help greatly appreciated, thank you
> Tim|||> Guest always exists in sysusers, it's just only enabled when
> you add the user. If it's not enabled in the database, the
> status is 0 in sysusers.
> -Sue
>
Thank you very much, I thought it must be something easy like that,
but somehow the status column in sysusers isn't documented in the
online help ("only for internal purposes") and I couldn't figure out
how to check if the guest user was enabled or not.
Have a nice day
Tim

Disabling guest account doesn't work as expected (please help!)

Hi all, I want to disable access of the SQL guest account except
master and tempdb. I tried it for one database now, but before I used
this script in query analyzer to check whether the guest account had
access whatsoever:
select has_dbaccess('DatabaseName') as Access,* from sysusers
and it gave me a 1 for the row corresponding to guest:
>1<20guest0x000x002000-08-06 01:27:55.187[...]
now I tried to disable it for the database via:
USE <DatabaseName
GO
EXECUTE sp_revokedbaccess guest
and I got an appropriate message that the account was disabled.
When I queried the sysusers table again as I did in the first
statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
this possible? In addition to this problem I tried to disable it again
for the same database, but now I get "User 'guest' does not exist in
the current database." which is actually that what I expected after
disabling the account, but why does it still show up in sysusers with
access to exactly the database that I disabled its access to!?!?!
Any help greatly appreciated, thank you
Tim
> select has_dbaccess('DatabaseName') as Access,* from sysusers
Note that there is no correlation in this query between HAS_DBACCESS and the
sysusers table so HAS_DBACCESS will have the same value for each row
returned. If your login has access to the specified database, it will
return 1 for all rows.
The guest account will always be in the sysusers table but can
enabled/disabled using sp_grantdbaccess/sp_revokedbaccess (or
sp_adduser/sp_dropuser). The guest account is disabled by default in user
databases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Gorgs" <google@.supertimmy.com> wrote in message
news:afa954a3.0404060807.509acc2a@.posting.google.c om...
> Hi all, I want to disable access of the SQL guest account except
> master and tempdb. I tried it for one database now, but before I used
> this script in query analyzer to check whether the guest account had
> access whatsoever:
> select has_dbaccess('DatabaseName') as Access,* from sysusers
> and it gave me a 1 for the row corresponding to guest:
> now I tried to disable it for the database via:
> USE <DatabaseName
> GO
> EXECUTE sp_revokedbaccess guest
> and I got an appropriate message that the account was disabled.
> When I queried the sysusers table again as I did in the first
> statement, I still get a 1 for has_dbaccess('DatabaseName'), how is
> this possible? In addition to this problem I tried to disable it again
> for the same database, but now I get "User 'guest' does not exist in
> the current database." which is actually that what I expected after
> disabling the account, but why does it still show up in sysusers with
> access to exactly the database that I disabled its access to!?!?!
> Any help greatly appreciated, thank you
> Tim
|||> Guest always exists in sysusers, it's just only enabled when
> you add the user. If it's not enabled in the database, the
> status is 0 in sysusers.
> -Sue
>
Thank you very much, I thought it must be something easy like that,
but somehow the status column in sysusers isn't documented in the
online help ("only for internal purposes") and I couldn't figure out
how to check if the guest user was enabled or not.
Have a nice day
Tim