4

I want to grant a user permission to create, alter, and execute stored procedures, but without the ability to create other database objects. I excluded them from the role db_ddladmin, but explicitly granted the permissions "Create procedure" and "Execute". These permssions appear in the effective permissions list. However, when I try to create a stored procedure with this login, I get the following error:

"The specified schema name "dbo" either does not exist or you do not have permission to use it."

Any suggestions?

swilliams
  • 48,060
  • 27
  • 100
  • 130
user17777
  • 341
  • 2
  • 4
  • 9

2 Answers2

5

A user can create procedures in a schema that they own. So you can set up a schema for the user to do development work. Then, if it needs to be dbo, the admin can put it there when development is done.

ScottStonehouse
  • 24,155
  • 7
  • 32
  • 34
  • 1
    Agreed. Create a new schema in which users will be able to create their sprocs. Grant them access to that schema, and you should be good to go! – Dave Markle Oct 23 '08 at 01:51
2

If you can create (or alter) and execute a stored procedure, you can do anything.

Execute on a stored procedure implies all other permissions within the context of that procedure. So if you can change a procedure and run it, there's no point in restricting anything else. You could just make the procedure act as a proxy on your behalf.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • That's not very good. Other DBMSes allow you to create stored procedures without having to necessarily have the authority to create other types of objects such as tables. You can't create a stored procedure to do things you don't have permissions for. So, for example, you could give someone the authority to create a stored procedure to read or update tables in the database but they wouldn't be able to do things like create tables or create a stored procedure to drop all of the tables in the database. – Rob at TVSeries.com May 20 '13 at 13:55
  • In SQL Server it's only unsafe if the user can create stored procedures in a schema owned by another user. Stored procedures run under the identity of the caller, or the owner (your choice), and suppress permissions checking to other objects owned by the stored procedure owner. So a user's own schema is a reliable sandbox. – David Browne - Microsoft Aug 27 '18 at 11:55