Granting Edit Permission on Stored Procedures

I have a user on my database that has the following base permissions :

public
db_datareader

I need to give this user permission to edit a single stored procedure. I have tried using the following command :

GRANT ALL ON stored_procedure_name TO username

Which executes successfully, but the user still cannot edit the stored procedure.

If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.

Does anybody know how I can do this?

EDIT : This is on SQL 2000Does this user have the same permission on some tables which related to this procedure?sql

Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth

Grant Views to User

Hi all,

I have a question on the grant issue. I have created a view base on three tables, and I grant 'select' to the user to see the views I have created. But when the user select this views, it say :
"SELECT permission denied on object 'Table1', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table2', database 'XX', owner 'XX'.
SELECT permission denied on object 'Table3', database 'XX', owner 'XX'."

I grant this three table to the user, and it run successfully.

Question is whether is there a way I can let user run the views but without seeing the three tables? or is there a way to hide from user?

Thanks in advance.

Are the tables in another database?

If the tables are in another database than the user, see if this applies (from BOL)

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

/Kenneth

|||

Hi Kenneth,

The views and tables are in the same database. But even in same database it give me the same problem that I have mention in my first post on this topic.

Thanks.

|||Are the views and the table owned by different users ? If they are owned by different users then you should see this error, otherwise it will be a security violation.|||

Hi,

Ya, the tables is created using one user account and the views is created by another user account.

So you mean that I need to create the views using the user account which create the tables? Or is there any other way to solve this?

Thanks.

|||

The 'old way' to avoid this kind of problems (broken ownership chains), is to have all objects in the database
being owned by the same user - eg 'dbo'

The easy way to accomplish that, is to always create objects with two-part names (dbo.myNewtable, dbo.myProc etc..)

/Kenneth