CODEWITHSUNDEEP
asp.netsql-server
IrishChieftain
IrishChieftain

Reputation: 15252

SQL Server Permissions

This is a typical SQL Server error, I know:

The EXECUTE permission was denied on the object
'GetStaffDirectoryListingsByCompany', database 'MyDB', schema 'dbo'.

I was able to fix it in SSMS by adding the EXECUTE permission for the 'IIS APPPOOL\DefaultAppPool'. I tried to add specific AppPool for this application 'IIS APPPOOL\MyAppPool' but could not locate it in SSMS. Why is this happening?

My question is simple: for a DB with dozens of stored procedures, how do I add this permission once instead of having to manually do it for each stored procedure?

I couldn't get the following to work:

https://msdn.microsoft.com/en-us/library/ms187940.aspx

UPDATE

I should point out some background to this problem:

First, my Win7 dev machine was upgraded to Windows 10 and somehow all the projects were associated with the DefaultAppPool in IIS (as well as their own App Pools).

Second, the DB was restored from a version on a production server to my local dev machine (maybe why permissions were messed up?).

Upvotes: 0

Views: 538

Answers (1)

Raul G
Raul G

Reputation: 329

You can either grant EXECUTE permission on the schema or on the whole DB, for example:

USE [myDatabase]
go

-- Grant EXECUTE permission on the schema dbo
GRANT EXECUTE ON SCHEMA::[dbo] TO [myUser]
go

or 

USE [myDatabase]
go

-- Grant EXECUTE permission on the DB
GRANT EXECUTE TO [myUser]
go

I hope this helps.

Following up to the comment by IrishChieftain:

Upon reading your particular scenario more carefully, I see that it seems like SSMS is not showing you IIS APPPOOL\MyAppPool on myDB database.

Most likely the root cause is that a different group is granting the application access to the DB. I am not an IIS expert, and I am not sure why granting access to the default pool worked (The SIDs should not have matched, could it be that your app is actually running under the default application pool?).

In any case, I would recommend trying the following: Replace myUser with the more specific pool IIS APPPOOL\MyAppPool; the command should create the user implicitly, and it should also start showing up in SSMS.

Please let us know if it worked, we can try a different approach.

Upvotes: 2

Related Questions