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

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

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

ALTER AUTHORIZATION ON SCHEMA::Myschema TO User3;
GO

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