(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get cluttered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will show the last
time a database was accessed? If not, is there a relatively simple SQL-based solution
that I could craft that could help track that?
Thanks for any help you can provide!
John Peterson
You have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com
|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.
|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
potentially log access to specific databases. I suppose I could have a "monitor" wake up
every X seconds and read the distinct databases that were in use -- but that seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses, I could
>potentially log access to specific databases. I suppose I could have a "monitor" wake up
>every X seconds and read the distinct databases that were in use -- but that seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:4kdpe010faeumj5c62626q6oo83iegva7m@.4ax.com.. .
>
|||Understood. How about reading database logs? Would *that* show any activity? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com... [vbcol=seagreen]
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
up[vbcol=seagreen]
of
>
|||Well...I noodled on using DBCC LOG to try and see if any of that information might be
germane, but I don't think so (everything is "reset" on a restart of SQL Server).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was last affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
http://www.experts-exchange.com/Data..._20684794.html
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? How
would[vbcol=seagreen]
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com...
wake[vbcol=seagreen]
> up
kind
> of
>
|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activity? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.4ax.com.. .
>up
>of
>
Showing posts with label allin. Show all posts
Showing posts with label allin. Show all posts
Wednesday, March 28, 2012
How to determine the last access date of a DB?
(SQL Server 2000, SP3a)
Hello all!
In my company, we have several development servers that, over time, get clut
tered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will sh
ow the last
time a database was accessed? If not, is there a relatively simple SQL-base
d solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
potentially log access to specific databases. I suppose I could have a "mon
itor" wake up
every X seconds and read the distinct databases that were in use -- but that
seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
>potentially log access to specific databases. I suppose I could have a "mo
nitor" wake up
>every X seconds and read the distinct databases that were in use -- but tha
t seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
>|||Understood. How about reading database logs? Would *that* show any activit
y? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
up[vbcol=seagreen]
of[vbcol=seagreen]
>|||Well...I noodled on using DBCC LOG to try and see if any of that information
might be
germane, but I don't think so (everything is "reset" on a restart of SQL Ser
ver).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was la
st affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html[/ur
l]
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? Ho
w
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
wake[vbcol=seagreen]
> up
kind[vbcol=seagreen]
> of
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activi
ty? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
>up
>of
>
Hello all!
In my company, we have several development servers that, over time, get clut
tered with a
*lot* of "scratch" databases. Is there some inherent mechanism that will sh
ow the last
time a database was accessed? If not, is there a relatively simple SQL-base
d solution
that I could craft that could help track that?
Thanks for any help you can provide!
John PetersonYou have to do some server level settings for that.
SQL Server can log event information for logon attempts and you can view it
by reviewing the errorlog. By turning on the
auditing level of SQL Server.
follow these steps to enable auditing of all/successfull connections with
Enterprise Manager in SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click all/success etc(required
option).
You must stop and restart the server for this setting to take effect.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com|||Login attempts won't tell you if a database has been used or
not. It will only tell you about server logins, not database
access. You would need to monitor this with profiler
going forward. I don't think there is any direct way to get
historical usage if you haven't been monitoring for such.
I suppose you could always drop the databases and then see
who calls complaining to determine if the databases are
being used. And you could use whatever criteria in
determining if you actually backup the databases before
dropping them. That could make for an interesting work day.
-Sue
On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
<REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>You have to do some server level settings for that.
>SQL Server can log event information for logon attempts and you can view it
>by reviewing the errorlog. By turning on the
>auditing level of SQL Server.
>follow these steps to enable auditing of all/successfull connections with
>Enterprise Manager in SQL Server:
>Expand a server group.
>Right-click a server, and then click Properties.
>On the Security tab, under Audit Level, click all/success etc(required
>option).
>You must stop and restart the server for this setting to take effect.|||Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
potentially log access to specific databases. I suppose I could have a "mon
itor" wake up
every X seconds and read the distinct databases that were in use -- but that
seems kind of
heavy handed...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
> Login attempts won't tell you if a database has been used or
> not. It will only tell you about server logins, not database
> access. You would need to monitor this with profiler
> going forward. I don't think there is any direct way to get
> historical usage if you haven't been monitoring for such.
> I suppose you could always drop the databases and then see
> who calls complaining to determine if the databases are
> being used. And you could use whatever criteria in
> determining if you actually backup the databases before
> dropping them. That could make for an interesting work day.
> -Sue
> On Thu, 8 Jul 2004 07:04:50 +0530, "Vishal Parkar"
> <REMOVE_THIS_vgparkar@.yahoo.co.in> wrote:
>
>|||Triggers on system tables isn't supported though and
sysprocesses is only materialized when used so there
wouldn't even be something to put a trigger on.
-Sue
On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks guys! Yeah...if I could put a trigger or something on sysprocesses,
I could
>potentially log access to specific databases. I suppose I could have a "mo
nitor" wake up
>every X seconds and read the distinct databases that were in use -- but tha
t seems kind of
>heavy handed...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:4kdpe010faeumj5c62626q6oo83iegva7m@.
4ax.com...
>|||Understood. How about reading database logs? Would *that* show any activit
y? How would
I even go about doing that?
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
> Triggers on system tables isn't supported though and
> sysprocesses is only materialized when used so there
> wouldn't even be something to put a trigger on.
> -Sue
> On Wed, 7 Jul 2004 21:52:56 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
up[vbcol=seagreen]
of[vbcol=seagreen]
>|||Well...I noodled on using DBCC LOG to try and see if any of that information
might be
germane, but I don't think so (everything is "reset" on a restart of SQL Ser
ver).
Then I looked at sysobjects.refdate, but that didn't pan out either.
Is there *any* way to determine the date/time that a particular table was la
st affected by
an INSERT/UPDATE/DELETE?
There seems to be a similar question here:
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20684794.html[/ur
l]
But I don't want to sign up just to find out I *can't* do this... ;-)
Thanks for any help you can provide! :-)
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23Cd7QnPZEHA.2944@.TK2MSFTNGP11.phx.gbl...
> Understood. How about reading database logs? Would *that* show any activity? Ho
w
would
> I even go about doing that?
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
wake[vbcol=seagreen]
> up
kind[vbcol=seagreen]
> of
>|||The SQL Server logs...not reliably. Unless the databases
have errors all the time that get logged. In terms of the
database log, I don't think you can't get timestamps using
dbcc loginfo or dbcc log. You can check object creation
dates in the database but that won't necessarily mean the
database isn't being used if no one is creating objects. And
even with the logs, if for some reason, they are only
selecting out of the database, that wouldn't do you any good
either.
If you want to read details of log files, you need to use
something like Log Explorer from lumigent -
www.lumigent.com
-Sue
On Thu, 8 Jul 2004 07:42:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Understood. How about reading database logs? Would *that* show any activi
ty? How would
>I even go about doing that?
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:q4eqe0htf3hbgg1hme377t5ro5gpcvs351@.
4ax.com...
>up
>of
>
Wednesday, March 7, 2012
How to delete Guest user, via group membership
Dear all
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
RegardsHi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...[vbcol=seagreen]
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||What version? 2000 or 2005?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
RegardsHi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...[vbcol=seagreen]
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||What version? 2000 or 2005?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegroups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegroups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
How to delete Guest user, via group membership
Dear all
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
Regards
Hi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...[vbcol=seagreen]
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:
|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards
|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegr oups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>
|||What version? 2000 or 2005?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards
|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegr oups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>
|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
In my databases I can see the user Guest, via group membership, but I
can not delete it by any way. Is there any way to delete it?
Regards
Hi
What does it mean via group membership?
Do you have permissions to drop logins?
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
|||delete the row from sysusers table.
shahdharti@.gmail.com wrote:
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
|||ch
It is really deprecate to edit system tables
"ch" <ch@.dontemailme.com> wrote in message
news:43A16AB2.932AD7A0@.dontemailme.com...[vbcol=seagreen]
> delete the row from sysusers table.
>
> shahdharti@.gmail.com wrote:
|||In Enterprise manager when I see users list I see the Guest user and in
status column I see 'via group membership'.
In sysusers system table I can found guest user.
sp_helpuser does not show guest user.
When I try to delete I get error :15008 user Guest does not exist in
the current database.
Regards
|||Hi
Why would you want to delete a rows from system table?
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegr oups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>
|||What version? 2000 or 2005?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
|||SQL Server 2000, SP3.
One more strange matter , when I register server in my coleagues PC ,
it is not showing Guest user there.
On my pc all servers which are registered I am seeing Guest user,
having status via group memeber ship.
I want to remove this guest for all servers from my PC.
Regards
|||The user doesn't really exists in the real sense. Try
SELECT * FROM sysusers WHERE name = 'guest'
You will see that the column hasdbaccess is 0.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<shahdharti@.gmail.com> wrote in message
news:1134653831.421300.138100@.z14g2000cwz.googlegr oups.com...
> In Enterprise manager when I see users list I see the Guest user and in
> status column I see 'via group membership'.
> In sysusers system table I can found guest user.
> sp_helpuser does not show guest user.
> When I try to delete I get error :15008 user Guest does not exist in
> the current database.
> Regards
>
|||Have you got the SQL2005 tools installed? The version of SQLDMO installed by
the SQL2005 tools issues a slightly different query to enumerate database
users than the SQL2000 versions. This has the side effect of the guest user
showing up in all databases when viewed via Enterprise Manager. If you open
EM on the server itself (assuming there are no SQL2005 components installed)
you should see that it doesn't show up (the default SQL2000 behaviour). This
is just a UI issue and nothing has actually changed on the server - it's
nothing to worry about.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
<shahdharti@.gmail.com> wrote in message
news:1134650134.830481.233610@.z14g2000cwz.googlegr oups.com...
> Dear all
> In my databases I can see the user Guest, via group membership, but I
> can not delete it by any way. Is there any way to delete it?
> Regards
>
Subscribe to:
Posts (Atom)