July 28, 2011 at 11:57 am
Here's a strange one.
The SQL Login has db_datareader on all the appropriate databases. Proc was created in DB1 (under schema dbo) and pulls from tables in DB2.
GRANT Execute ON Schema::dbo TO MySQLLogin was run on DB1.
I can connect to SSMS using MySQLLogin and execute the proc just fine. I even tested this using Execute AS. So far as we can tell, there are no superceding DENY perms.
But when we try to connect to the db through a report on a website, we get back "Execute permission denied..." on the proc in question. Any thoughts?
July 28, 2011 at 10:22 pm
Go into the properties of the database user. Go into securables and inside there, you should be able to view the stored procedure object. If you can't, browse for it and then select it. Then when you look at the stored procedure in the GUI you will be able to see the "effective" permissions. What are they listed for that stored procedure?
July 29, 2011 at 1:52 am
Brandie Tarvin (7/28/2011)
Here's a strange one.The SQL Login has db_datareader on all the appropriate databases. Proc was created in DB1 (under schema dbo) and pulls from tables in DB2.
GRANT Execute ON Schema::dbo TO MySQLLogin was run on DB1.
I can connect to SSMS using MySQLLogin and execute the proc just fine. I even tested this using Execute AS. So far as we can tell, there are no superceding DENY perms.
But when we try to connect to the db through a report on a website, we get back "Execute permission denied..." on the proc in question. Any thoughts?
From your report, have you made sure that the user you have granted access to is the same user mentioned in the data source of that report. Please confirm if the data source credentials are the same.
Regards
Chandan
July 29, 2011 at 5:14 am
Ahha. Here's an interesting twist.
It wasn't the SQL Login's problem at all. The website uses a domain account to access SQL Server and that domain account didn't have proper permissions on the db. So even though the report's data source was the SQL Login, which had execute perms on the proc, the domain account was taking precedence somehow.
We've fixed the issue by giving the domain account the needed permissions. Thanks everyone for your thoughts.
July 29, 2011 at 5:31 am
Brandie Tarvin (7/29/2011)
Ahha. Here's an interesting twist.It wasn't the SQL Login's problem at all. The website uses a domain account to access SQL Server and that domain account didn't have proper permissions on the db. So even though the report's data source was the SQL Login, which had execute perms on the proc, the domain account was taking precedence somehow.
We've fixed the issue by giving the domain account the needed permissions. Thanks everyone for your thoughts.
Can you please explain what you meant by saying that the website was using a domain account?
July 29, 2011 at 6:15 am
chandan_jha18 (7/29/2011)
Can you please explain what you meant by saying that the website was using a domain account?
The website that hosts the reports also pulls data from various SQL Server tables for other things (website images, etc.). To access SQL Server, it uses a windows domain account in its credentials.
July 29, 2011 at 6:23 am
Brandie Tarvin (7/29/2011)
chandan_jha18 (7/29/2011)
Can you please explain what you meant by saying that the website was using a domain account?The website that hosts the reports also pulls data from various SQL Server tables for other things (website images, etc.). To access SQL Server, it uses a windows domain account in its credentials.
sorry for my confusion. We already have a login specified in the report\data source which is a sql login you mentioned.So why the website login needs access? And when you mean website, do you mean some IIS or ASP.net service credentials?
Chandan
July 29, 2011 at 7:12 am
chandan_jha18 (7/29/2011)
Brandie Tarvin (7/29/2011)
chandan_jha18 (7/29/2011)
Can you please explain what you meant by saying that the website was using a domain account?The website that hosts the reports also pulls data from various SQL Server tables for other things (website images, etc.). To access SQL Server, it uses a windows domain account in its credentials.
sorry for my confusion. We already have a login specified in the report\data source which is a sql login you mentioned.So why the website login needs access? And when you mean website, do you mean some IIS or ASP.net service credentials?
Don't assume my system architecture is going to be the same as your system architecture. If your sql login is working for your website reports, then you are good to go. You shouldn't make changes to your system based on what someone else is doing in theirs.
My system architecture is complicated and I am not a standalone server / DBA admin. Different teams have different responsibilities in my organization. So while I know that there is a domain login used by the website, and what it is accessing on my servers, I don't have any of the actual website details. I can't really answer your question because I didn't set any of that stuff up and I don't manage it. My responsibility ends at the database servers, making sure they are working properly, and that the website people aren't getting more permissions than they absolutely need.
Sorry, but I'm unable to clarify things for you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply