question

Therealist avatar image
Therealist asked

How to grant permission for a user only on a specific stored procedure on a database

Hi Everyone, How to grant read/write permission for a user only on a specific stored procedure on a database. NOTE: permission has to be granted only on a stored procedure not all stored procedures and any other database objects. Is it like... USE DBNAME GRANT EXECUTE ON 'spname' TO 'username' or am i missing anything And what are the differences between a database schema and a database object Thanks in advance
stored-proceduresuserspermission
10 |1200

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

1 Answer

·
KenJ avatar image
KenJ answered
You have the GRANT syntax correct, though you could prepend the schema name GRANT EXECUTE ON dbo_or_some_other_schema.spname TO 'username' A database schema can be thought of as a container inside of which database objects reside. Permissions applied at the schema level propagate to all objects within the schema. Permissions granted at the object level are specific to that object within that schema. By default, database objects are created in a database user's default schema (oftentimes 'dbo' or a schema named with the user's name) select * from dbo.tablename execute dbo.procedurename or select * from some_other_schema.tablename execute some_other_schema.procedurename
10 |1200

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.