[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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

What is the syntax for me Granting Access to a newly created SCHEMA? I want all users to be able to do anything within this Schema.
0
MIKE
Asked:
MIKE
1 Solution
 
lcohanDatabase AnalystCommented:
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'
0
 
Russell FoxDatabase DeveloperCommented:
If your Public server role is active:
GRANT CONTROL ON SCHEMA :: YourSchema TO PUBLIC;

Open in new window

0
 
Mandeep SinghDatabase AdministratorCommented:
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

Open in new window

0
 
Scott PletcherSenior DBACommented:
I wouldn't give CONTROL, as I wouldn't want them altering the authorization on the schema itself ... or just dropping the schema!

It's somewhat lengthier, but I'd explicitly list the permission(s) I wanted them to have:

GRANT
    ALTER, --unavoidable, unfortunately, since it has big security risks as well
    EXECUTE, INSERT, DELETE, UPDATE,
    SELECT, REFERENCES, VIEW CHANGE TRACKING, VIEW DEFINITION
ON SCHEMA::schema_name
TO public
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now