Permissions in SQL

1

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

2

all kinds of permissions are possible ..

3

#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.

4

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

5

why do you need to be read only?

6

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