Cannot grant execute permission for some SPs under different schema in a DB

Question

Hi,

Im trying to give execute access for all SPs to a windows login in a particular DB. Once i gave the access it can execute all SPs under DBO schema.

There are few SPs created under seperate schema. Those SPs the login cannot execute. when i try to give access explicitly like 'grant exec on spname to loginname' , im getting this error

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

I also tried to add the SPs to the securables, tat is also not helping. Please help me.

Thanks in Advance

Answer 1

What is your login's permission? If a login is member of the db_owner role (which is not the same as dbo, i.e. owner of the database) it can still be denied access to some objects.

Does it work?

GRANT EXEC ON SCHEMA::schemaname TO loginname

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 2

Hi Uri,

The login  is a ddl_admin. I have already tried this

GRANT EXEC ON SCHEMA::schemaname TO loginname

---

its not working(throwing the same error).  I have tried in ssms . i added the sps to the securables list for the user also added to the securables for the particular SPs. but its not getting reflected.

Thanks

Answer 3

Now you see why it is :-)

• The db_ddladmin role can create, drop, and alter objects within the database, regardless of who the owner is.
• The db_ddladmin role cannot alter security.

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 4

Hi Uri,

I apologize for the above information. Im the sysadmin of the server and the windows login to which im trying to give permission is a ddl_admin.

Thanks

Answer 5

You say you are trying to grant database-level permissions to a login. Database-level permission can only be granted to a database user. But that's not likely the cause of your problem, or you wouldn't be getting that message. The name of the user is probably the same as the name of the login, so your statement is OK.

The error says you can't grant permission to "sa, dbo, entity owner, information_schema, sys, or yourself." My guess is that the user is either dbo or the owner of the schema.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty