question

Rod avatar image
Rod asked

SQL accounts to create views/SP's

In SQL Server 2005, is it possible to create a SQL account which can create/edit views and stored procedures, but not create or modify table schemas? If so, how is that done?

sql-server-2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Yes, if you have a look at the GRANT Database Permissions page on MSDN, you'll see that there are permissions CREATE VIEW, and CREATE PROCEDURE. You just grant those permissions to the principal involved. Alternatively, you could DENY CREATE TABLE - whichever suited your scenario best.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

In addition to what Matt has noted, there is a role called db_ddl_admin. If you grant access through that role to a login, they'll be able to manipulate database objects. You can either grant access through this and then revoke certain behaviors, or just specifically grant access as Matt suggested. Either way works.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.