Granting SELECT...INTO permissions

Question

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?

Answer 1

The INTO clause requires both the CREATE TABLE and the ALTER permissions*, so you'll need to grant ALTER permission on the archive schema (or ALTER ANY SCHEMA on the database). The ALTER permission is required as you're ultimately creating a table.

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;

---

* Before June 2024, the docs for INTO didn't mention the ALTER permission, but this has now been fixed.