July 12, 2006 at 8:51 am
Hello
I need to limit all the users ( except the sysadmins ) in the following way :
They should be able to make their regular work , but together with that they will not be able to change the size of their databases.
There are several options close for this solution but no close enought using the database existing roles:
1) dbowner -> in this case user will be able to change the size while will be able to do anything else with database .
2) db_ddladmin + db_datareader + db_datawriter -> in this case user will not be able to set the database size , but will not be able to execute any dbo. stored procedure.
For my opinion to solve a problem in need to create some database role that will deny access to part of tables.This role with a mix of db_owner will solve a problem.
The following questions i have met till now :
1) how to create global database role ( database role that will be available for all databases ).I have tried to add the database role for model db , but when i have assigned user to this role in newly created database permissions was not copied.
2) what permissions should be denied to disallow for customer changing the database size + he will not be remove this deny.
Thanks for reply.
July 13, 2006 at 3:14 am
Why do you want to make them db_owner? Users should never be part of the do_owner role.
Create your own role, add all users to that role and grant the role execute on all stored procedures. Pair that with datareader and datawriter as needed.
That way, they can do their word, but can't change DB settings or permissions or create objects
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply