SQL Server 2012 how to create new schema from existing database selecting only subset of database objects?

Question

I'm looking for a way to create new schema from existing database selecting only subset of database objects. I.e. I want to have some "database representation" which only includes subset of tables explicitly specified. What is the easiest way to create schema which includes/has access not to all objects in the database. Is it possible at all?
I'm trying to do this in MS SQL Server 2012 R2.

Answer 1

You can create a new schema and expose the objects you need with a synonym:

USE master;
GO

IF DB_ID('testDatabase') IS NOT NULL 
BEGIN
    ALTER DATABASE testDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE testDatabase
END
GO

CREATE DATABASE testDatabase
GO

USE testDatabase;
GO

-- test table in schema dbo
CREATE TABLE dbo.test (
    i int primary key clustered
)
GO

INSERT INTO dbo.test VALUES (1),(2),(3)
GO

-- new schema for exposed objects
CREATE SCHEMA schema1 
GO

-- synonym for dbo.test
CREATE SYNONYM schema1.test FOR dbo.test
GO

-- new user with no permissions on the dbo schema
CREATE USER testUser WITHOUT LOGIN
GO

-- grant permissions on the new schema only
GRANT SELECT ON schema::schema1 TO testUser
GO

-- change security context to testUser
EXECUTE AS USER = 'testUser'
GO

SELECT * FROM schema1.test; -- works

SELECT * FROM dbo.test; -- fails

-- revert to the original security context
REVERT 
GO