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.
1 Answer
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