Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
can't believe there is no fix for SQL Server 2008. really annoying. thanks for your help. –  gsharp Jul 27 '11 at 11:12
    
It's also not working with SQLServer 2008 R2. –  gsharp Aug 2 '11 at 6:41
add comment
up vote 0 down vote accepted

There's a workarround described in the Link of Sankar.

That works both with SQLSever 2008 and 2008 R2.

GRANT view definition on schema : "schemaname" to "username"
share|improve this answer
    
If Sankar's answer was good, his should be marked as such and yours as a comment ;-). –  Marian Aug 2 '11 at 7:12
    
Hmm no. It helped me to find the solution, but his answer is not correct to solve my problem. So the downvote on my post is in my opinion wrong ;-) –  gsharp Aug 2 '11 at 7:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.