how to drop 1 users tmp objects

I need to drop a user but the user owns objects, apparently tmp tables, how
can I dlete them?
Thanks in advance!Are you on SQL Server 2000? I assume so because of your symptom.
Find all the objects by querying sysobjects for any objects owned by the
user's userid (dbo.sysusers.uid). Either drop those objects or change their
owner using sp_changeobjectowner.
If the user still will not drop, perhaps it also owns some user-defined
datatypes. Those also need to be dropped.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||Are you referring to killing the connection and getting rid of #temp tables?
If so, they should be
removed when the connection is terminated.
If you mean removing the users, and that user owns regular tables in the dat
abase, use DROP TABLE to
get rid of those tables (if that is what you want to do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:572AD28D-BABD-4F34-B849-A36C1C1F00C4@.microsoft.com...
>I need to drop a user but the user owns objects, apparently tmp tables, how
> can I dlete them?
> Thanks in advance!|||after running the DROP TABLE and getting another error, I am not sure these
objects are tables.
Their naming convention is "TMP_SYSA_1234"
and I am running SQL Server 2K
thanks for your fast responses!|||and there are no connections
"totoro" wrote:

> after running the DROP TABLE and getting another error, I am not sure thes
e
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||Ok, assuming that you are querying sysobjects to find these, then sysobjects
also contains a code showing you the type of object:
U = user table
P = stored procedure
V = view
et cetera.
You will need to use the proper DROP statement for whatever the object is.
In Enterprise Manager you can go to each of the collections in the database
(Tables, Views, etc) and sort by owner to find any that are not dbo. Then
you can delete them from there.
RLF
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
> after running the DROP TABLE and getting another error, I am not sure
> these
> objects are tables.
> Their naming convention is "TMP_SYSA_1234"
> and I am running SQL Server 2K
> thanks for your fast responses!|||there is a "U"
but the drop table command retrieved an error saying it couldn't find any
table with the name (TMP_SYS_etc) I had in the query, so my sysntax must be
wrong
lurnin a lot tuday!
"Russell Fields" wrote:

> Ok, assuming that you are querying sysobjects to find these, then sysobjec
ts
> also contains a code showing you the type of object:
> U = user table
> P = stored procedure
> V = view
> et cetera.
> You will need to use the proper DROP statement for whatever the object is.
> In Enterprise Manager you can go to each of the collections in the databas
e
> (Tables, Views, etc) and sort by owner to find any that are not dbo. Then
> you can delete them from there.
> RLF
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:0CEFA857-150F-4108-BC3A-420F9F68BBC8@.microsoft.com...
>
>|||Did you include the owner of the object?
DROP TABLE ownername.tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"totoro" <totoro@.discussions.microsoft.com> wrote in message
news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...[vbcol=seagreen]
> there is a "U"
> but the drop table command retrieved an error saying it couldn't find any
> table with the name (TMP_SYS_etc) I had in the query, so my sysntax must b
e
> wrong
> lurnin a lot tuday!
> "Russell Fields" wrote:
>|||THAT DID IT! Thanks Tibor and Russel, you guys rock!
"Tibor Karaszi" wrote:

> Did you include the owner of the object?
> DROP TABLE ownername.tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "totoro" <totoro@.discussions.microsoft.com> wrote in message
> news:F494E330-47FA-471F-B85C-2DD1ACA811C8@.microsoft.com...
>