Prevent users to create temp (#) tables on SQL server

Hello All,
I have to remove temp tables (# or ##) create permissions for the logins
which do not have associated users in tempdb (using guest account). I have
tried to deny "Create Table" explicitly for the public account but no luck.
I
have also tried to create that user in tempdb and then denied "create table"
on tempdb to this user but no luck again.
While investigating more, when I queried on tempdb..sysobjects, I found that
these temp tables are created with UID = 1 (dbo), not able to understand why
the temp tables are being created as dbo and not with that user account. Any
idea how I can prevent any user/role to create temp tables?
Thanks in advance
- AnilHello Anil,
Event Data function might help you. Check the following links for further
details.
http://msdn2.microsoft.com/en-us/library/ms187909.aspx
http://blogs.msdn.com/mrorke/archiv.../25/456354.aspx
Cheers!
MB
"anilksharma99" <anilksharma99@.discussions.microsoft.com> wrote in message
news:E2BCCD74-D8A7-4ACC-993C-7F50C7AB947D@.microsoft.com...
> Hello All,
> I have to remove temp tables (# or ##) create permissions for the logins
> which do not have associated users in tempdb (using guest account). I have
> tried to deny "Create Table" explicitly for the public account but no
> luck. I
> have also tried to create that user in tempdb and then denied "create
> table"
> on tempdb to this user but no luck again.
> While investigating more, when I queried on tempdb..sysobjects, I found
> that
> these temp tables are created with UID = 1 (dbo), not able to understand
> why
> the temp tables are being created as dbo and not with that user account.
> Any
> idea how I can prevent any user/role to create temp tables?
> Thanks in advance
> - Anil|||Thanks MB for your response. I went through below links but they are
applicable to SS 2005, we are using SS 2000 (SP4) in our production
environment currently.
Thanks
Anil
"MB" wrote:

> Hello Anil,
> Event Data function might help you. Check the following links for further
> details.
> http://msdn2.microsoft.com/en-us/library/ms187909.aspx
> http://blogs.msdn.com/mrorke/archiv.../25/456354.aspx
> Cheers!
> MB
> "anilksharma99" <anilksharma99@.discussions.microsoft.com> wrote in message
> news:E2BCCD74-D8A7-4ACC-993C-7F50C7AB947D@.microsoft.com...
>
>|||anilksharma99 (anilksharma99@.discussions.microsoft.com) writes:
> Thanks MB for your response. I went through below links but they are
> applicable to SS 2005, we are using SS 2000 (SP4) in our production
> environment currently.
Why do you want to revoke the rights to create temp tables? I am fairly
certain that if you were able to do this, that more than application would
break down.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx