Sql-server – Hide schemas from users in MSSQL

azure-sql-databasedatabase-designsql server

I'm creating a database with a few different schemas.

99% of users connect using Excel, and when they do they see all the system stuff:

This just confuses the users and I would like to configure SQL Server so that they only see UK_data and UK_reports.

So far I tried denying select on the things that I want to hide and only allowing select on the UK_ schemas, but it didn't work.

Is there a way to hide them from users?

Thanks!

Best Answer

I maybe found two solution for you:

HIDE

Hide Table "Person.Address"

EXEC sp_addextendedproperty
@name = N'microsoft_database_tools_support',
@value = 'Hide',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

Show Table "Person.Address"

EXEC sp_dropextendedproperty
@name = N'microsoft_database_tools_support',
@level0type = N'Schema', @level0name = 'Person',
@level1type = N'Table', @level1name = 'Address';
GO

DENY

`DENY VIEW DEFINITION ON Schema.Table TO UserName;`

Now UserName won’t be abe to see Table in Object Explorer. In Fact, they won’t be able to see the table in sys.tables or INFORMATION_SCHEMA.TABLES.

You can Use the DENY keyword to deny certain Users and REVOKE to Remove the existing permission.

Related Solutions

Sql-server – Setting up users and admins with schema privileges

The part that should interest you is the grants. But I figured a "code/picture" is worth a thousand words.

use master
go
create database test1
go
use test1
go
create schema data
go
create schema ref
go
create table data.tbl1(a int);insert data.tbl1 values(1);
go
create table ref.tbl2(a int);insert ref.tbl2 values(2);
go

create proc data.tbl1_select as select * from tbl1
go
create proc data.tbl1_insert(@a int) as insert tbl1 values(@a)
go
create proc data.tbl1_update(@a int) as update tbl1 set a = @a
go
create proc data.tbl1_delete as delete from tbl1
go

create proc ref.tbl2_select as select * from tbl2
go
create proc ref.tbl2_insert(@a int) as insert tbl2 values(@a)
go
create proc ref.tbl2_update(@a int) as update tbl2 set a = @a
go
create proc ref.tbl2_delete as delete from tbl2
go

create role users
go
create role admins
go
grant execute on schema::data to admins
grant execute on schema::ref to admins
grant execute on schema::data to users
grant execute on ref.tbl2_select to users
go

use master
go
create login user1 with password='', default_database=test1, check_expiration=off, check_policy=off
create login user2 with password='', default_database=test1, check_expiration=off, check_policy=off
go
use test1
go
create user user1 for login user1;
create user user2 for login user2;
alter role admins add member user1;
alter role users add member user2;
go

setuser 'user1'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
go
setuser -- To go back to your user
go
setuser 'user2'
go
exec ref.tbl2_update 3
exec ref.tbl2_select
exec data.tbl1_update 3
exec data.tbl1_select
select * from ref.tbl2

Here's the result:

The EXECUTE permission was denied on the object 'tbl2_update', database 'test1', schema 'ref'.
The SELECT permission was denied on the object 'tbl2', database 'test1', schema 'ref'.

Sql-server – Users cannot view tables in non-default schema in SSMS

Short answer: Don't use db_datareader or db_datawriter or their deny equivalents. They are for backwards compatibility only. Using them will cause issues like the one you are facing.

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in schema Sales then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::Sales TO Alice ;

If you want to give principal Alice the SELECT, INSERT, UPDATE and DELETE permissions to all table-valued objects in all schemas then use the following.

GRANT SELECT, INSERT, UPDATE, DELETE TO Alice ;

Metadata visibility will then work correctly.

Best Answer

Related Solutions

Sql-server – Setting up users and admins with schema privileges

Sql-server – Users cannot view tables in non-default schema in SSMS

Related Question