0

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.

1 Answer 1

0

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.

1
  • 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, 2011 at 18:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .