Wednesday, June 2, 2010

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 
   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.

Monday, May 31, 2010

Hey there!

Hi everyone :)
This is my first post here, though not first post in my life. My old blog is here:

I decided to move my blog to have more flexibility and perhaps slightly better blogging platform. I am going to continue writing about SQL Server, problems that I faced and sometimes solved, tips and tricks, coding practices and a lot more - we'll see what future will bring.

I hope that you will find this blog interesting :)