3

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.

5

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.