Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

db_ddladmin and the SSMS table designer

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:

DDLAdminSSMSOE1

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:

DDLAdminSSMSOE2

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...