Friday, June 5, 2009

Restricting Users to a Schema in a Shared Database

Our healthcare analysts do lots of "data smashing," so they needed a work area where they could play with the data. Initially we were creating individual databases for each user, but this quickly proved difficult to manage. So we decided to create a single sandbox database and separate users/projects by schemas.

The main stumbling block was to figure out how to grant all of the rights that a database owner would normally have, but only in a specific schema. After some experimenting with SQL Server, here is what worked for us:

-- create the work area (Schema)
create schema HEDIS authorization dbo
GO

-- create user that will be restricted to the HEDIS schema
create user TESTUSER
for login TESTUSER
with DEFAULT_SCHEMA = HEDIS

GO

-- Grant various create rights to the user
-- with grant (the user can authorize others)

grant create table, create view, create procedure,
create function, create synonym

to TESTUSER
with grant option

GO

-- Grant control over the schema
grant control on schema::HEDIS
to TESTUSER with grant option
GO


That's it. Let me know if anyone knows a different/better way to do it or if I missed some rights/permissions.

No comments: