Granting SELECT…INTO permissions

azure-sql-databasepermissionssql server

I've created a new schema, archive, and am able to create, read, update and delete tables within it.

I have an EXTERNAL_USER who I'm trying to give permissions to so that they can carry out select..into on the schema.

I've given them the CREATE TABLE permission on the database and the SELECT permission on the schema, but they're getting the "The specified schema name 'archive' either does not exist or you do not have permission to use it" error message.

The query they're trying to run is:

select *
into archive.post
from core.post

The query runs fine for me.

I've also tried giving them the db_datareader and db_datawriter roles on the database, with no difference.

I'm absolutely out of ideas – what am I not thinking of?

Best Answer

The documentation for the INTO clause says:

Requires CREATE TABLE permission in the destination database.

But CREATE TABLE says:

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

You're ultimately creating a table, so you'll need to grant ALTER permission on the archive schema (or ALTER ANY SCHEMA on the database).

The INTO documentation should probably say, "requires the same permissions as CREATE TABLE in the destination database."

As you've noticed, you'll need to grant separate permissions if you want your user to be able to read or write the tables they create in the archive schema.

Demo:

CREATE SCHEMA archive;
GO
DROP USER IF EXISTS Bob;
GO
CREATE USER Bob WITHOUT LOGIN;
GO
CREATE TABLE dbo.Test (c1 integer NULL);
INSERT dbo.Test (c1) VALUES (1234);
GO
GRANT CREATE TABLE TO Bob; -- ON DATABASE::YourDB
GRANT ALTER ON SCHEMA::archive TO Bob;
GRANT SELECT ON dbo.Test TO Bob;
GO
EXECUTE AS USER = 'Bob';

    -- Success
    SELECT T.c1 
    INTO archive.Test 
    FROM dbo.Test AS T;

    -- Error: no SELECT permission
    SELECT * FROM archive.Test AS T;

REVERT;
GO
DROP TABLE dbo.Test;
DROP TABLE archive.Test;
DROP SCHEMA archive;
Related Question