If you want to grant a user the ability to create/alter/delete any table, SP, function etc in a database you have a several options. For example:

• You can grant all of the CREATE permissions either to the database itself or to all of the schemas.
• You can add the user to the db_ddladmin role
• You can add the user to the db_owner role

I’m sure if you tried you could come up with several other options but these are the ones that come immediately to mind. The first one sounds really complicated to me. The third one is way more power than you want to grant unless it’s absolutely needed. That leaves the second, which in my opinion is really the way you want to go. In fact db_ddladmin was specifically designed for this type of security role.

Interestingly if you grant add someone to the db_ddladmin role and they try to go into the table designer in SSMS they are going to see the following warning:

No big deal really. This is just a warning and doesn’t actually say you won’t be able to make changes, just that you might not have sufficient permissions.

However, even more interestingly if you are using SSMS 2008R2 then you are also going to see the following error:

At this point you really do have a problem. There is a bug in the table designer that will not allow a user that is not at least a member of the db_owner role to modify tables. I’m currently on 2008R2 SP2 and I have no idea if this will be fixed in SP3 but I’m not holding my breath. There is a closed connect item on this that indicated it would be fixed in a future version (and it is fixed in SQL 2012) but didn’t mention a fix being released in a service pack. Also after some superficial testing I believe this error will occur at ANY level of permissions lower than membership in the db_owner role.

If you happen to be the type that prefers to use code over the GUI like I am, you may never notice this. Or for that matter if you are not living in the past like I am you won’t notice it either. Unfortunately it was a real shock to me when one of my users ran into it. So be warned!

Filed under: Microsoft SQL Server, Problem Resolution, Security, SQLServerPedia Syndication, SSMS Tagged: database permissions, microsoft sql server, problem resolution, security, SSMS