debugMinor
Role db_ddladmin not working properly on SQL Server 2008
Viewed 0 times
2008db_ddladminproperlysqlworkingroleservernot
Problem
I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner.
I thought I could do it with the db_ddladmin role, but somehow that doesn't wort as expected. The error messages i get is that are like this:
etc
Any idea how i can achieve my goal?
I thought I could do it with the db_ddladmin role, but somehow that doesn't wort as expected. The error messages i get is that are like this:
Your are not loggend on as the database owner or system administrator.
You might not beable to sache changes to tables that you do not own.
Table dbo.MyTable is set to read only,
user doesn't have enough right on this table.etc
Any idea how i can achieve my goal?
Solution
EDIT:
I re-read your question and I may have got it incorrect before. Looks like you are running the scripts using SSMS instead of using T-SQL scripts. I verified that it works when using T-SQL scripts and doesn't work when using SSMS GUI. To get around this issue, you may want to use T-SQL scripts.
This is a known issue and looks like this is fixed in DENALI CTP3 and I can confirm that.
Ref: https://connect.microsoft.com/SQLServer/feedback/details/467582/db-ddladmin-role-doesnt-allow-use-of-design-functions-in-ssms
Just verified the (correct) behavior also works in SQL Server 2008 R2. I don't have a SQL Server 2008 instance to verify unfortunately.
I re-read your question and I may have got it incorrect before. Looks like you are running the scripts using SSMS instead of using T-SQL scripts. I verified that it works when using T-SQL scripts and doesn't work when using SSMS GUI. To get around this issue, you may want to use T-SQL scripts.
This is a known issue and looks like this is fixed in DENALI CTP3 and I can confirm that.
Ref: https://connect.microsoft.com/SQLServer/feedback/details/467582/db-ddladmin-role-doesnt-allow-use-of-design-functions-in-ssms
Just verified the (correct) behavior also works in SQL Server 2008 R2. I don't have a SQL Server 2008 instance to verify unfortunately.
Context
StackExchange Database Administrators Q#4081, answer score: 3
Revisions (0)
No revisions yet.