SQL Server - Disabled User Account Overrides AD Group Authorization!

Just a quick note on a scenario I ran into, and what I did to fix it:

Scenario

  • A user was running a stored procedure that created a table.
  • Rather than having a table created in the "dbo" schema, it was creating a table with their user name as the schema, as in: DOMAIN\Username.TableName.
  • The user was authorized via an AD group login.
  • The user's individual user name was a disabled "User" in the database they were accessing. 

Fixing

  • I specified the default schema to "dbo" in the AD account.  I did this first and this alone did not fix the issue.  The disabled user account's settings overrode this setting.
  • Removed the objects in the database in their non-dbo schema.
  • Removed schema (within the database, security, schema folder).
  • Dropped the username

Perhaps changing the default schema on the disabled account would have also helped, but I didn't try that.  There are only so many hours in a day.

Tags: 

Categories: