Sql-server – Hide Schemas and Tables

permissionssql serversql-server-2008-r2ssms

I have the following structure:

Views

  • dbo.vSales
  • Sec.vSales

Tables

  • dbo.Security
  • Sec.Products
  • Sec.Countries
  • Sec.Users

I have 2 groups accessing my Database:

  • Administrators
  • Standard_User

The users are connecting using SQL Server Management Studio.

I want the Standard_User group to only to see the Sec schema, and only be able to do stuff in this Schema. At the moment, when they login they can see everything inside the database even if they don't have rights to execute or select.

Is this possible?

Best Answer

The "do stuff" part

Create a role, and give it the permissions you want:

USE YourDatabase;
GO
CREATE ROLE StandardUsers;
GO
GRANT ALTER, SELECT, INSERT, UPDATE, DELETE, EXECUTE
  ON SCHEMA::Sec TO StandardUsers;
-- you have to grant object-level rights at the database level
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, CREATE FUNCTION
  TO StandardUsers;

Now add the users you want to that role:

EXEC sys.sp_addrolemember @rolename = N'StandardUsers',
  @membername = N'database_principal_name';
-- rinse, repeat

(Note that ALTER ROLE is much better syntax for managing role members, but that syntax wasn't added until SQL Server 2012.)

The "see stuff" part

Management Studio for the most part will not show users objects they are prohibited from seeing. If you do the following to the letter, and then:

CREATE LOGIN splat WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE USER splat FROM LOGIN splat;
GO
EXEC sys.sp_addrolemember @rolename = N'StandardUsers', @membername = N'splat';

Now, connect to Object Explorer with this new login, and go into your database. If you expand the tables node, you should only see tables in the Sec schema.

If you see others, then it might be that this security layer was implemented in a later version of Management Studio (I'm using 2016 RC0, and don't have a 2008 R2 version anywhere within reach). If that's the case, you could always upgrade (the 2012 and 2014 versions can manage 2008 R2 just fine, as long as they aren't doing exotic things like SSIS packages).

Related Question