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

  1. Set up users in bulk via [NT AUTHORITY\Authenticated Users]
  2. 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
  3. They then execute it as the following actions occur:
    1. The Schema is created
    2. A login is created for the user (remember we are logged in via Authenticated Users)
    3. A User is created to match the Login with the above schema being the default
    4. 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

The advantage of this approach is that nothing is created until the user uses it.

 

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

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape