SQL Server 2012 - How to Grant Access to a Schema for ALL USERS?

USE [YourDB]
GO
CREATE ROLE [SchemaAccess] AUTHORIZATION [YourSchemaNameHere]
GO

ALTER AUTHORIZATION ON SCHEMA::[YourSchemaNameHere] TO [SchemaAccess]
GO

--or to create new schema

CREATE SCHEMA [YourSchemaNameHere1] AUTHORIZATION [SchemaAccess]
GO


--then just add all users you want to that [SchemaAccess] DB Role - you can put this in a dynamic sql.

sp_addrolemember 'SchemaAccess', 'User1'
sp_addrolemember 'SchemaAccess', 'User2'

...
sp_addrolemember 'SchemaAccess', 'UserN'

If your Public server role is active:

GRANT CONTROL ON SCHEMA :: YourSchema TO PUBLIC;

Select allOpen in new window

Hi,

Russell Fox is right, you can also use it:

Granting SELECT permission on schema Person to database user JILL

GRANT CONTROL ON SCHEMA :: YOURSCHEMA TO JILL

Select allOpen in new window