CODEWITHSUNDEEP
sqlsql-serverssmspowerbi
LeChuck
LeChuck

Reputation: 71

Prevent all users from accessing tables/views under a schema

I am connecting to my SQL studio manager using Power BI (a report writing software). I only want tables appearing listed under a certain schema, and deny permission to access all others, instead of displaying all tables which it currently does.

Usually, when preventing individual users from accessing a schema, I would use the following code: 

revoke select on schema::UnwantedSchema to User
grant select on schema::WantedSchema To User

However, now I want it so ALL users and Logins have these permission settings. Not just the individual user. Is there a way I can do this without having to set the permissions for every individual user?

Upvotes: 0

Views: 1038

Answers (1)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

If you wanted to set the privileges to multiple user logins, You need to create a role and assign the role to the ers to that role. The give required permission to the role created.

Following are the sample steps.

--Create a new role
EXEC sp_addrole 'yourRole'
GO 

--Assiging role to the user
EXEC sp_addrolemember 'yourRole', 'yourUser';
GO

--Assigning permissions to the Role
GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::YourSchema TO yourRole;
GO

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO yourRole;
GO

Upvotes: 1

Related Questions