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



.



Relevant Pages

  • Re: Which Method to Create a Database Do I Use?
    ... To be fair, VS is a "generic" tool designed to front a variety of backend databases, but each of the serious engines have rights management needs as well. ... But the more I think about how connecting to a database is setup and the trouble it's been, the more it just pisses me off. ... Hitchhiker's Guide to Visual Studio and SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Which Method to Create a Database Do I Use?
    ... when I was working on the team) to get rights management tools integrated ... Hitchhiker's Guide to Visual Studio and SQL Server ... actually be able to connect to the database. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Which Method to Create a Database Do I Use?
    ... Okay, once the database is built, you have to get rights to access it. ... SSPI and "SQL Server" ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Assume SQL Server Rights for apps, any cons?
    ... Since your customer is the sysadmin on his SQL Server box, ... >administrates the underlying SQL Server 2000 database by himself. ... >wants that my software assumes the user rights automatically from the SQL ... >My application offers a GUI to manage customers. ...
    (microsoft.public.dotnet.framework)
  • Re: Newbie: I dont understand user permissions for table access
    ... >My database is remote to my workstation. ... >> HOW are you connecting to SQL Server? ... >> If you are using NT auth, what rights does your NT ... the PUBLIC role? ...
    (microsoft.public.sqlserver.server)