GDB @ 10.1 - Why is db_ddladmin no longer sufficient?

[TrevorHart1]

Hi,

Im not sure if this is a 10.1 issue or a SQL Server 2008 R2 issue.

In 10.0 (and earlier) I would always grant my SDE user db_reader, db_writer and db_ddladmin which was sufficient to create the geodatabase schema.

At 10.1 (on SQL 2008 R2) the EnableEnterpriseGeodatabase tool tells me I need to grant the following (even though the user already has db_ddladmin)

GRANT CREATE TABLE,
  CREATE PROCEDURE,
  CREATE VIEW,
  CREATE FUNCTION
TO SDE;

Once I grant these I can enable the geodatabase to an existing database.

I haven't found a decent listing of what db_ddladmin gives you but this role has always been sufficient in the past.

Any ideas?

Thanks

[AsrujitSengupta]

Trevor,

While looking into the issue, I came across the below link, which provides details about all the Permissions of Fixed Database Roles:

http://msdn.microsoft.com/en-us/library/ms189612(v=sql.105).aspx

So, it seems that the permissions "CREATE TABLE, CREATE PROCEDURE, CREATE VIEW, CREATE FUNCTION" are granted along with the "db_ddladmin" role.

I also came across the below link, which mentions that "Role db_ddladmin not working properly on SQL Server 2008" (I know you mentioned 2008R2)...but still, you might want to check this as the link mentions some issues with 2008 R2 too:

http://dba.stackexchange.com/questions/4081/role-db-ddladmin-not-working-properly-on-sql-server-2008

Hope this helps!