Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

migrated from Sep 21 '11 at 15:34

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

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

Your Answer


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.