Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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
0
Sonali P
Asked:
Sonali P
  • 2
1 Solution
 
TempDBACommented:
I am not sure of any other way how it will be done, but below should work:-

As database roles are database specific but not schema specific,
-> Create user for the login with the default schema as dbo
-> add specified role to the user
-> Then for other schemas, use

GRANT <PERMISSION> ON <SCHEMA> TO <GRANTEE>
DENY  <PERMISSION> ON <SCHEMA> TO <GRANTEE>
REVOKE <PERMISSION> ON <SCHEMA> TO <GRANTEE>

<PERMISSION> = SELECT | UPDATE | REFERENCES
0
 
Sonali PDatabase AdministratorAuthor Commented:
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
0
 
Sonali PDatabase AdministratorAuthor Commented:
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
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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