Question of the Day

Changing the Schema

I set up a few users on my SQL Server 2022 instance.

CREATE LOGIN User1 WITH PASSWORD = 'Demo12#1'
CREATE USER User1 FOR LOGIN User1
GO
CREATE LOGIN User2 WITH PASSWORD = 'Demo12#2'
CREATE USER User2 FOR LOGIN User2
GO
CREATE LOGIN User3 WITH PASSWORD = 'Demo12#3'
CREATE USER User3 FOR LOGIN User3
GO

I then created a schema that one of them owned. Under this schema, I added a table with some data.

CREATE SCHEMA MySchema AUTHORIZATION User1
GO
CREATE TABLE Myschema.MyTable(myid INT)
GO
INSERT MySchema.MyTable
(
    myid
)
VALUES
(1), (2), (3)
GO
SELECT * FROM MySchema.MyTable
GO

I granted rights and verified that User2 could access this table.

GRANT SELECT ON Myschema.MyTable TO User2
GO
SETUSER 'USER2'
GO
SELECT * FROM MySchema.MyTable
GO

This worked. Now, I move this schema to a new user.

ALTER AUTHORIZATION ON SCHEMA::Myschema TO User3;
GO

What happens with this code?

SETUSER 'USER2'
GO
SELECT * FROM MySchema.MyTable
GO

See possible answers