Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a system win server 2008/ sql server 2008 r2 where sql server is accessed by remote user (the same but multiple connections).

I need to have a user who can create/alert databases but I do not want to put him in sysadmin.

When I add user to public and dbowner role that is enough to run query and create databases but he can not alter database. Granting user explicit permission to alter has no effect. The only time it works is when I put user in sysadmin role.

When user creates database from remote location (c# exe) I need to alter several options because I do not need transaction logs for example.

share|improve this question
add comment

migrated from stackoverflow.com Sep 21 '11 at 15:34

This question came from our site for professional and enthusiast programmers.

1 Answer

Please see the following articles: server-level roles and database-level roles, they are good primers on standard MSSQL security.

The dbcreator role should let a user define a new database and its required files, if that's not sufficient, then you should look into the diskadmin role.

share|improve this answer
    
by some reason does not work in my case, gave user all db level roles on master/model (except deny) also dbcreator and diskadmin. I am thinking this might be some unique weird case. Thank you for reply. –  Vlad Sep 21 '11 at 18:15
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.