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!
|||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 these
> 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 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...
>
>
|||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...
>