Grant access only to a Schema in SQL

Question

Hi All,

I have a Database called TM1 and in this database I have 2 schemas. One is called TMS1 and another is called TM1App.

There are 5 views under schema TMS1. For Example: TMS1 .View1, TMS1 .View2....TMS1 .View5. These views are reading data from a table under Schema TMS1.
CREATE VEIW TMS1.View1
AS

SELECT * FROM TMS1 .Table1

There are another 5 views under schema TM1App. TM1App.View6, TM1App.View7....TM1App .View10. These views are reading data from views under Schema TMS1

CREATE VEIW TM1App.View6
AS

SELECT * FROM TMS1 .View1

I have create a SQL account TM1_User for end user and want to give Dataread access only to schema TM1App so that end user should see and access only objects under schema TM1App.

I tried DENY SELECT ON SCHEMA :: [TMS1] TO [TM1_User]; but this does not allow to read data from TM1App.View6. It says "The SELECT permission was denied on the object 'View1', database 'TM1', schema 'TMS1'." If I Grant Select permission schema 'TMS1 then end user can see and execute objects under schema 'TMS1 also which I don't want.

Answer 1

Hi Shiv,

You do not use DENY permission as this will stop access to any Linked objects. Also do not grant any Permission using GUI to database level as this Grant access to all Objects.

Simply follow the below steps to grant SCHEMA level access.

CREATE SQL USER
GRANT only PUBLIC access to Database
Now run the below command to grant permission
GRANT SELECT ON SCHME :: <Schema Name> TO <User Name>

This will work for you.

Answer 2

Hi @Shivendoo Kumar ,

You could GRANT schema permissions that are effective for everything existing and everything that will exist in that schema.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>;  

Further to that, if you want to then deny permissions on a certain object within that schema, you can do.

DENY INSERT ON OBJECT::<schema>.<object> TO <user>;  

But in your situation, there is no need for you to deny the permission on that user.

Please refer one example from below:

create database TM1  
  
use TM1  
  
create schema TMS1  
create schema TM1App  
  
create table TMS1 .Table1  
(ID int,  
Name varchar(10))  
  
insert into TMS1 .Table1 values  
(1,'Name01'),  
(2,'Name02')  
  
SELECT * FROM TMS1 .Table1  
  
CREATE View TMS1.View1  
AS  
  
SELECT * FROM TMS1 .Table1  
  
select * from TMS1.View1  
  
CREATE VIEW TM1App.View6  
AS  
  
SELECT * FROM TMS1 .View1  
  
select * from TM1App.View6  
  
CREATE LOGIN TM1Login WITH PASSWORD = 'Password123';  
  
create user TM1_User for login TM1Login  
  
GRANT SELECT ON SCHEMA :: TM1App TO TM1_User;  
  
Execute As User='TM1_User'  
GO  
  
SELECT * FROM TMS1 .View1  
--Msg 229, Level 14, State 5, Line 41  
--The SELECT permission was denied on the object 'View1', database 'TM1', schema 'TMS1'.  
  
select * from TM1App.View6  
--ID Name  
--1 Name01  
--2 Name02  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.