Re: Minimum Permission Create table in TempDB



Joe,

Being a guest user does not give you any rights in particular. It is just a way to be a database user without specifically making someone a user.

As a guest user you acquire any rights either granted to the public role or directly to the guest user.

So you still would need to give CREATE TABLE rights to public or to guest or to the specific user.

Another thing about creating users in tempdb is that tempdb itself is recreated each time the SQL Server restarts. So, you lose those settings at each restart.

It is possible to have a startup stored procedure (created in master and marked by the sp_procoption procedure) that would recreate needed rights in tempdb. You should read about that in the BOL.

RLF


"Joe K." <Joe K.@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:08D4A785-6611-41D9-942E-A573C44B3615@xxxxxxxxxxxxxxxx

Why does guest user account enabled within database give other users the
ability to create tables within tempdb database?

Thanks,


"Erland Sommarskog" wrote:

Joe K. (Joe K.@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> What is the minimum permission required to create a table within TempDB
> in a SQL Server 2000 database server?

For a temp table, none beyond access to the server.

For a regular table, you need the same permission as in any other
database. That is you need the CREATE TABLE permission or must be a
member of the db_ddladmin database role. And you must be a user in that
database, unless the guest user is enabled.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx



.