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.
Friday, June 5, 2009
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment