Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Tim
Starting Member
392 Posts |
 Posted - 2003-06-30 : 01:45:44
|
| I've got a situation where I have four sp's that I want users to be able to change and/or execute. They should not be able to delete or create any sp's. The sp's are owned by dbo and need to stay that way. as other applications reference them with "dbo.sp_foobar"Any ideas how to give the permission? |
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-06-30 : 02:17:09
|
| If you check books online you will find the following entries about procedure permissionsCREATE PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles. Members of the sysadmin fixed server role and the db_owner fixed database role can transfer CREATE PROCEDURE permissions to other users. Permission to execute a stored procedure is given to the procedure owner, who can then set execution permission for other database users.ALTER PROCEDURE permissions default to members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the owner of the procedure, and are not transferable.DROP PROCEDURE permissions default to the procedure owner and are not transferable. However, members of the db_owner and db_ddladmin fixed database roles and the sysadmin fixed server role can drop any object by specifying the owner in DROP PROCEDURE.Basically, you can define who has create procedure permissions, but you cannot individually say who can alter or drop a procedure (the "not transferable" bit).Why would you want a user to alter a procedure anyway?You can give people permissions to execute a procedure by clicking the Permissions button or using the GRANT command in SQL |
 |
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-06-30 : 02:24:59
|
| Thanks, I read all that in BOL which is why I posted here... to see if anyone has a way around those limitations.The real example is a database that some developers can hit with Crystal Reports. In some cases they use stored procedurs as the basis of reports. In order to manage the stored procedures, I want to force them to go through DBA to get them created, but then they can alter them as much as they like after that.I also want the stored procedures to be owned by dbo, without giving the developers dbo permissions. |
|
|
|
lee_h
Starting Member
36 Posts |
Posted - 2003-06-30 : 02:45:36
|
| I know what you are saying, but the restrictions are there for a very good reason. One of your none dbo developers could create/alter a procedure to look like this.alter procedure dbo.sp_exampleasEXEC sp_addrolemember 'dbo', 'Jeff'goand make themselves dbo.The restrictions prevent users doing things like this. |
|
|
|
|
|
|
|
|
sp permissions