Permissions in SQL

sqlor · February 14, 2021, 3:06pm

Hello

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

Is it possible to:

have permission to create my own temp tables which will be visible only to me
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

harishgg1 · February 14, 2021, 3:43pm

all kinds of permissions are possible ..

ScottPletcher · February 14, 2021, 4:12pm

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

sqlor · February 14, 2021, 7:01pm

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

yosiasz · February 14, 2021, 7:14pm

why do you need to be read only?

robert_volk · February 15, 2021, 1:24pm

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.