Simplifying Security Using Schemas

One of my favorite aspects of schemas (which were introduced in 2005) is the ability easily manage permissions. For those getting caught in terminology, schemas are the new abstraction layer that replace object owners. In SQL 2000, every object had an owner. And that owner was a user. In most cases the user was dbo. So your stored procedures would have a naming convention of dbo.spExecuteMe. With schemas, the model has changed.

While object owners were actual users, schemas are not. Schemas are abstract objects used to separate owners from the objects. The beauty of this is that you can assign a user access to an entire schema, which may be comprised of hundreds of objects. Instead of assigning permissions at the object level, you can say, “Any user assigned to the role of ‘ExecApp’ will be able to execute any object that is created within the schema ‘App’. This way, by simply adding a user to the ExecApp role, he will inherently be able to execute any procedure in the application schema. Likewise, any procedure created within the schema ‘App’, will automatically be able to execute that procedure.

So how do you do it? Start by Creating a schema.

CREATE SCHEMA App

Next, transfer stored procedures to this schema, or simply create stored procedures with this schema.

To transfer:

ALTER SCHEMA App TRANSFER dbo.spGetCustomers

Lastly, create a role that will be allowed to execute procedures in the App schema; Add a user to the role; and then allow the role to execute anything on the schema App.

CREATE ROLE [ExecApp] AUTHORIZATION [dbo]

EXEC sp_addrolemember N'ExecApp', N'JohnJacobs'

GRANT EXECUTE ON SCHEMA::[App] TO [ExecApp]

Viola!

2 comments
najib 25 Jun 2014 at 11:12 am

ok i’m i would tell something can help this site is to add facebook and g+ comment palce below this post comment to give more choice to comment because this feild is not usufull this days poeples need speed to give com and go out thank you

Derek Dieter 08 Jul 2014 at 4:04 pm

Thanks Najib, good idea

Featured Articles

 Site Author

  • Thanks for visiting!