Friday, February 17, 2012

Database with Single user mode

Hi,
I have a problem, where in a particular database is in single user mode (after it was restored). Now somebody in the network has logged onto this database as "sa", because of which am not able to loggin, even though I know "sa" username/pwd. Is there a way of killing the connected user session, so that I can login? Also, would it be possible to find, from which machine it was logged on earlier?
Thanks very much
GYKGYK
The problems with letting people have SA access huh. If
someone is logged on using sa, you should try to find out
what they are doing, rather than just kill them. Or are
you one of those places where everyone knows and uses the
sa account?
Either using sp_who or viewing process info through
enterprise manager you can see Host Name, ie the PC they
are logged on from. How easy that is to resolve depends on
how accessable that info is in your company.
Regards
John|||Hi John,
Thanks for the response. This server is a development server, and unfortunately every user has sa access.
Now that the database is in single user mode, and somebody else has already logged in, I am not able to login. So there is no question of using sp_who system proc. I basically, want to know if there is any method by which one can login into a database with single user mode, when other use has already logged in.
Or let me frame my question like this..
what if a user has logged into a single user mode database and go home. Does this mean other users can't login at all? I guess there should be some alternative..
Thanks
GYK
-- John Bandettini wrote: --
GYK
The problems with letting people have SA access huh. If
someone is logged on using sa, you should try to find out
what they are doing, rather than just kill them. Or are
you one of those places where everyone knows and uses the
sa account?
Either using sp_who or viewing process info through
enterprise manager you can see Host Name, ie the PC they
are logged on from. How easy that is to resolve depends on
how accessable that info is in your company.
Regards
John|||The question is whether the server is in single user (-m) or the database.
If the server: You shouldn't run the server in this mode normally. Only when
restoring the master database.
If the database: Kick out the user (KILL).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:427A3F1F-685C-4F42-8194-D7127D3454CF@.microsoft.com...
> Hi John,
> Thanks for the response. This server is a development server, and
unfortunately every user has sa access.
> Now that the database is in single user mode, and somebody else has
already logged in, I am not able to login. So there is no question of using
sp_who system proc. I basically, want to know if there is any method by
which one can login into a database with single user mode, when other use
has already logged in.
> Or let me frame my question like this..
> what if a user has logged into a single user mode database and go home.
Does this mean other users can't login at all? I guess there should be some
alternative..
> Thanks
> GYK
> -- John Bandettini wrote: --
> GYK
> The problems with letting people have SA access huh. If
> someone is logged on using sa, you should try to find out
> what they are doing, rather than just kill them. Or are
> you one of those places where everyone knows and uses the
> sa account?
> Either using sp_who or viewing process info through
> enterprise manager you can see Host Name, ie the PC they
> are logged on from. How easy that is to resolve depends on
> how accessable that info is in your company.
> Regards
> John
>|||You can still get the info, run sp_who from master
database or use enterprise manager. You are getting
process information for the entire server, you don't need
to be in the user database.
Have you tried to issue a kill? In enterprise manager
double click the process id, see if you get the option to
kill. An alternative might be to get your NT/Windows admin
to kill the network connection based on the host id.
Hope this helps
John