Save the time – use roles!

Some time ago I wrote about coding practices. I would like to follow this topic with a quick post about making the life easier.
If you happen to be a developer who also is responsible for managing development databases and access of other team members to the environment, you might find this tip useful.

Create role for developers and assign rights to it.

Usually you would want developers to be able to run DDL commands and also execute procedures and write and read data. I’ve seen many times that developers were given the db_owner role. This is in most cases too much. If you have specific custom role, you can easily adjust the security settings without having to browse through all users.

   1: use TestDB

   2: go

   3: create role [DBDeveloper] authorization dbo

   4: go

   5: grant execute to DBDeveloper

   6: go

   7: exec sp_addrolemember 'db_ddladmin', 'DBDeveloper'

   8: exec sp_addrolemember 'db_datawriter', 'DBDeveloper'

   9: exec sp_addrolemember 'db_datareader', 'DBDeveloper'

  10: go

As you see above, I create role DBDeveloper and grant several rights to the role. Next, I just need to add users to the role to give them what they need.

   1: use master

   2: go

   3: create login [frank] from windows with default_language=us_english 

   4: go

   5: use TestDB

   6: go 

   7: 

   8: create user [frank] from login [frank] with default_schema=dbo

   9: go

  10: exec sp_addrolemember 'DBDeveloper', 'frank'

  11: go

This little trick saves a lot of time and hassle, and if you save these scripts carefully, you can easily restore security on a database refreshed from production for example.

Technorati Tags: SQL Server,T-SQL,tips and tricks,programming