asked on

Permissions

Hi,

I need help setting up these permissions for the database.
I would like to assign these window groups to have different permissions for each schema in the database .
Please advise.

Thanks
schemas.xlsx

ASKER CERTIFIED SOLUTION

TempDBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SP_2018 .

ASKER

Thanks,

I tried this but i would ike to give individual permissiont o each schema for that user.
it only works with the permissions given to the default schema for that user/role

SP_2018 .

ASKER

Hi,

This has been resolved,
After giving individual permissions on schema , had to grant create permissions
on tables,stored procs ,views and functions for control or alter permission on the schema
to work

USE databasename;
GO

GRANT control ON schema::dbo to data_ad
GRANT control ON schema::esp to data_ad
GRANT control ON schema::ebs to data_ad

GRANT select ON schema::dbo to data_curer
GRANT control ON schema::esp to data_cure
GRANT control ON schema::ebs to data_cure

GRANT select ON schema::dbo to data_read
GRANT select ON schema::esp to data_read
GRANT control ON schema::ebs to data_read

Grant SHOWPLAN TO data_ad
Grant SHOWPLAN TO data_cure
Grant SHOWPLAN TO data_read

Grant VIEW DEFINITION TO data_ad
Grant VIEW DEFINITION TO data_cure
Grant VIEW DEFINITION TO data_read
----------------------------------------------------------------------------------------------------
GRANT CREATE TABLE TO data_ad
GRANT CREATE TABLE TO data_cure
GRANT CREATE TABLE TO data_read

GRANT CREATE VIEW TO data_ad
GRANT CREATE VIEW TO data_cure
GRANT CREATE VIEW TO data_read

GRANT CREATE FUNCTION TO data_ad
GRANT CREATE FUNCTION TO data_cure
GRANT CREATE FUNCTION TO data_read

GRANT CREATE PROCEDURE TO data_ad
GRANT CREATE PROCEDURE TO data_cure
GRANT CREATE PROCEDURE TO data_read