SQL Server 2005’s Schema Problem–An Elegant Solution

There’s a problem with schemas in SQL2K5 that I encountered again this week, and I put my head to it and developed a very elegant solution that I thought I’d share with everyone.

Problem:

When you connect to SQL through membership in a Windows group, you cannot define a default schema for the group. This means that if you want everyone to be in a specific schema, you can’t do it by default. When users connect through membership in a Windows group, SQL will automatically create a separate user (mapped to the AD acct), and a schema of the same name. So say you belong to the ‘Sales’ group in AD, and that group has rights in SalesDB. When you connect for the first time, SQL will create a user in that DB called domain.username and map it’s default schema to domain.username as well. This causes you to have tons of user accts in your DB that you have no use for because you’re supposed to be connecting through your group ‘Sales’.

What you want is for all of your people to create their objects in ‘dbo’. As it stands you have 2 choices by default to handle this.

1. You can just map the users directly in SQL and forget about putting them in a Windows group first.

2. You can go in every now and then and change the default schema for all the new users. But you can see how that would become unmanageable after just a very short while.

OK, all of this is a huge shortcoming in the way MS handles its schemas. Don’t get me wrong, I understand that they’re trying to protect against someone belonging to several groups that all have different default schemas defined… because how would SQL decide what your default schema is if you have several? But I also think there are other ways around this problem that would suit our needs much better. For starters, just don’t force new users into their own schema. In SQL, if you don’t define a default schema, it defaults to ‘dbo’, so in this case that would solve the issue from their side very well. In fact, everything should default to ‘dbo’ unless otherwise specified.

Solution:

OK, I’ve got a couple solutions of varying elegance.

1. Create a job that changes all of the default schemas for all of the new users. Just take the code below and paste it into a job step window for the DB you want it to run in. If you have several DBs, then add a new step for each DB and it’ll just cycle through all of them. It only takes the new ones by looking at the ones where the name = default_schema. It’s that easy, and you can change them to whatever you like. The logic is at your whim. Anyway, here’s the code.

DECLARE @currUser varchar(100),

@SQL nvarchar(200)

DECLARE Users CURSOR

READ_ONLY

FOR select [NAME] from sys.database_principals

where

[principal_id] > 5 AND

[type] = ‘U’ AND

[name] = [default_schema_name]

OPEN Users

FETCH NEXT FROM Users INTO @currUser

WHILE (@@fetch_status -1)

BEGIN SET @SQL = ‘ALTER USER [‘ + @currUser + ‘] WITH DEFAULT_SCHEMA = dbo’

EXEC (@SQL)

–print @SQL

FETCH NEXT FROM Users INTO @currUser

END CLOSE Users

DEALLOCATE Users

OK, that’s not too bad. It’ll work for a while at least. The problem is though that anyone can just come along and disable the job and you wouldn’t know it for a long time. So I’ve created another method for dealing with this and it’s actually much better. It won’t correct the default schema issue, but it will keep objects in the right schema.

2. Create a database-level DDL trigger to move the object to the new schema. I’ve created this trigger that will transfer an object to ‘dbo’ once it’s created. Now, you can obviously put them anyplace you like, and since I’ve only done it for SPs, you can expand it to any object. The point here is that you can do it and it doesn’t have to be manual.

Here’s the code:

CREATE TRIGGER EnforceSchema

ON DATABASE

WITH Execute AS ‘sa’

AFTER CREATE_PROCEDURE

AS

DECLARE @SQL char(100)

DECLARE @eventdata AS XML, @ProcName AS SYSNAME, @schemaname AS SYSNAME,

@eventtype AS NVARCHAR(100), @msg AS NVARCHAR(MAX), @abortflag AS BIT;

SET @eventdata = eventdata();

SET @ProcName = CAST(@eventdata.query(‘data(//ObjectName)’) AS SYSNAME);

SET @schemaname = CAST(@eventdata.query(‘data(//SchemaName)’) AS SYSNAME); SET @SQL = ‘ALTER SCHEMA dbo TRANSFER ‘ + ‘[‘ + @schemaname + ‘]’ + ‘.’ + @ProcName

EXEC (@SQL)

GO

OK, so just run that in the DB you’re trying to protect and you’ll be fine. There is something you should know about this though. If you don’t want users to have this kind of permissions, you have to run it as a different user. I chose ‘sa’. The problem is, and I don’t know why they did it this way, they probably just coded this bug into it… anyway, the problem is that it only checks the current DB for the user to run it as, so you have to specifically add ‘sa’ to the current DB and give it db_owner rights. OK, you probably don’t have to give it db_owner, but it’s already a sysadmin, so just shotgun the rights and don’t worry about getting it fine-grained. However, you can run it under anything you like, but realize that the acct you run this trigger under has to be given explicit rights in the current DB, it can’t just be a sysadmin.

OK guys, I hope this helps someone out there. Let me know if you like this solution and I’ll post more stuff like this as I develop it.