Doing my security audit, which i wrote about yesterday (https://sqlnotesfromtheunderground.wordpress.com/2014/06/18/query-to-find-all-dbs-schemas-jobs-objects-owned-by-users-on-an-instance/ ). I’ve found a couple of occasions a database will have a schema which is named the same as user (Domain\User). This is known action of poor coding and using Windows Groups. When you create any object you should full qualify it with the schema name dbo.MyTable . This is a good example of why!

Scenario:

You have a database where you give access to users via a Group and for some reason they need to create objects.

Issue:

When ever a user from the group creates an object without naming the schema in the database, the instance creates a schema with the name of the user and puts the object in it.

Example:

Create a domain group TestGroup, add a single user
Add a Login for the TestGroup to an Instance (set Default Schema to dbo)
Create a test database on the Instance
Add the Test Group as a db_owner to the test database
Login as the the user from the Test Group and create a table like so:

CREATE TABLE TestTable (
     col varchar(200)
)

now check the Databases Security -> Schemas

you will see a new schema with the same name as the user that ran the code.

schema

 

 

Running my Security Audit check you would see:

schema2

 

Solution:

The solution is easy. always create objects with the schema named:

CREATE TABLE dbo.TestTable (
     col varchar(200)
)

 

 

 

Advertisements