Permissions

Question

Dear All,

I've created a user and given them ”databasereader" and "databasewriter" permissions, but in addition I need them now to be able to do the following:

-Create/Delete/Update Tables, Create/Delete/Update Stored Procedures, Create Views/Delete/Update, Create Functions/Delete/Update

I was wondering if it is just easier to give themddl_admin permission or is there another way I would be able to give them permissions. Of course I'm not looking for the easiest was but the more secure process.

Thank you in advance!

Answer 1

IMHO

easier to add use to ddl_admin role instead of micro managing the permission.

Answer 2

But a better idea to get in the habit of using more granular permissions. See the permissions poster http://go.microsoft.com/fwlink/?linkid=229142

Grant the CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, and CREATE FUNCTION permissions.

GRANT the ALTER permission in the schema you expect that user to use to hold those objects.

GRANT the INSERT, UPDATE, and DELETE permissions, probably at the SCHEMA level.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 3

That depends if the user needs to be able to create objects in all schemas, or only in a specific schema. If the user only needs access to a certain schema, grant the specific permissions on schema level.

If the user needs to be able to create objects in dbo grant the specific permissions on database level.

You could use db_ddladmin, but there is always the question if you get something extra with it.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Hi,

If you were to add the user to the ddl_admin database role you need to be aware that you're allowing the user a way to elevate their privileges (ref: http://akawn.com/blog/2012/02/why-you-should-be-cautious-with-the-dbo_owner-role/)

Although it is typically more work to implement and maintain, using GRANT http://msdn.microsoft.com/en-us/library/ms178569.aspx with the required privileges is the best practice way forward.