How to grant access to multiple schemas in one go in SQL Server

Question

I'm very green when it comes to the world of database management in SQL Server.

Below is my SQL code to create a login & user & grant permissions

USE TestDb
GO

CREATE LOGIN [TestLogin] 
       WITH PASSWORD = N'123', DEFAULT_DATABASE = [TestDb], 
       CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF
GO

CREATE USER SqlUser FOR LOGIN [TestLogin]

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: dbo  to SqlUser

This runs fine & create user/login. He can access the dbo schema.

But I need to specify multiple schemas in the SCHEMA options.

So I tried :

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: [dbo,app]  to SqlUser

But I get an error:

Cannot find the schema 'dbo;app', because it does not exist or you do not have permission.

How can I specify multiple schemas in the above SQL pattern to grant access to the user?

Thanks!

Answer 1

You would have to run multiple statements:

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SqlUser
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO SqlUser

If this is something you do regularly, or several users need these permissions, you'd be better off creating a ROLE, and then adding users to that ROLE. This will enable you to do it in a single statement, with ALTER ROLE instead.

CREATE ROLE SqlRole; --Give a better, more appropriate name
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SqlRole;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO SqlRole;

GO

ALTER ROLE SqlRole ADD MEMBER SqlUser;