SQLTeam.com | Weblogs | Forums

Permissions in SQL

Hello

I get the error:
CREATE TABLE permission denied in database 'db'.

Is it possible to:

  1. have permission to create my own temp tables which will be visible only to me
  2. do not have permission to amend or delete any other tables

How can I do that?

Or is there a workaround where I can create temporary personal tables without having to request extra access rights?

Thanks

all kinds of permissions are possible ..

#1. No. Sysadmins, db owners and other high-authority logins and/or users will be able to see any table(s) you create.

#2. Yes, you can be explicitly DENYd permissions to other tables (and objects) in the db.

Thanks, is there a way to create temp tables (I only want to use them in my current query) with read only access?

why do you need to be read only?

In Microsoft SQL Server, a temporary table is specified with a # prefix character:

CREATE TABLE #myTable(col1 int not null);

Such a table is only accessible/visible in the session that created it. A global temporary table uses ## prefix:

CREATE TABLE ##globalTable(col2 int not null);

The global temp table can be accessed by another session. Both types of temporary tables are automatically destroyed when all sessions that accessed them terminate.

The single # temporary table would meet your read-only needs, no one else can access it. Any table created without a # or ## prefix is not a temporary table, and has the accessibility that @ScottPletcher described.

1 Like