Create a User Work Area Database on SQL Server
Long ago, in a past millennium (literally), I was working for Microsoft Licensing and we needed to create a User Work Area where power users can create their own tables, stored procedures, etc. A colleague from those days asked me for a modern equivalency. I like this idea because it amounts to crowd-sourcing of data. People own their data and can share it with whoever they wish easily from a massive repository. Of course, IT Micro-Managers may not be too happy because it may threaten some of their position-justification. I like the concept because it can make a firm more agile with better sharing of data, power users leveraging power user.
No more politics, let us get to the proposed pattern and then the code.
- The idea is to create a SCHEMA for each person and then grant him all of the permissions for his SCHEMA.
- He thus has his sandbox…
- The person (super user in terms of skills) can thus grant permission to other users to access his SCHEMA – sharing.
- If the Schema is the same as their domain name (value of SYSTEM_USER), one level of indirection is eliminated resulting in a better user experience.
- With Windows Authentication, SYSTEM_USER is the Microsoft/KenL and User may be ‘dbo’ or ‘guest’
This means that we need to create USER for each SYSTEM_USER. We also need to create a matching SCHEMA and assign this as the default SCHEMA.
Pattern #1: User Create as Needed
- Set up users in bulk via [NT AUTHORITY\Authenticated Users]
- Users login and execute a procedure (the only thing that they can do) to create their schema and permissions.
GRANT EXECUTE ON CreateMyWorkSpace TO [NT AUTHORITY\Authenticated Users] AS DBO - They then execute it as the following actions occur:
- The Schema is created
- A login is created for the user (remember we are logged in via Authenticated Users)
- A User is created to match the Login with the above schema being the default
- The user is granted full privileges to the schema.
The code below is the conceptualization (code is not working yet)
CREATE PROC CreateMyWorkSpace
AS
DECLARE @Schema nvarchar(max)
DECLARE @CMD nvarchar(max)
IF NOT EXISTS (SELECT 1 FROM Sys.Schemas where name=SYSTEM_USER)
BEGIN
SET @CMD='CREATE SCHEMA ['+SYSTEM_USER+'] AUTHORIZATION DBO'
EXEC (@CMD) AS LOGIN='LAPTOPX4\ken.lassesen'
SET @CMD='CREATE LOGIN ['+SYSTEM_USER+'] FROM WINDOWS WITH DEFAULT_DATABASE=[UserArea], DEFAULT_LANGUAGE=[us_english]'
EXEC (@CMD) AS LOGIN='LAPTOPX4\ken.lassesen'
SET @CMD='CREATE USER ['+SYSTEM_USER+ '] FOR LOGIN ['+SYSTEM_USER+'] WITH DEFAULT_SCHEMA = ['+SYSTEM_USER+']'
EXEC (@CMD) AS LOGIN='LAPTOPX4\ken.lassesen'
SET @CMD='GRANT CONTROL ON SCHEMA :: ['+SYSTEM_USER+'] TO ['+SYSTEM_USER+'] WITH GRANT OPTION AS dbo'
EXEC (@CMD) AS LOGIN='LAPTOPX4\ken.lassesen'
END
go
Pattern #2: Pre-Create Users
In this case, a table (or XML) is read and all of the above actions occur. This may result in many schemas being created that will never be used.
The effective code is below:
CREATE SCHEMA [Laptopx4\oltpitgn] AUTHORIZATION DBO
CREATE LOGIN [Laptopx4\oltpitgn] FROM WINDOWS WITH DEFAULT_DATABASE=[UserArea], DEFAULT_LANGUAGE=[us_english]
CREATE USER [Laptopx4\oltpitgn] FOR LOGIN [Laptopx4\oltpitgn] WITH DEFAULT_SCHEMA = [Laptopx4\oltpitgn]
GRANT CONTROL ON SCHEMA ::[Laptopx4\oltpitgn] TO [Laptopx4\oltpitgn] WITH GRANT OPTION AS dbo
Bottom Line
There’s some debugging to do on Pattern #1. The effective code for Pattern #2 does work but with one critical oddity. I can Insert, update, delete, select an existing table in the Schema – but cannot create one. Help is unclear on this aspect (http://msdn.microsoft.com/en-us/library/ms187940.aspx )… This needs further researching – stay tune…
One Work Around
The non-create behavior can be walked around by granting CREATE privileges (non-schema specific) and then adding a DDL trigger to abort any create except in the user schema. It is not as elegant as doing a schema specific GRANT for CREATE.
Comments
Post a Comment