May 9, 2008 at 12:17 am
I am using SqlDataSource in ASP.NET 2.0 (VS2005) application. I can use both stored procedures and in-line SQL statements within the development environment and everything works perfectly. However, when I publish the web site, only the stored procedures work and in-line SQL statements give 'Permission Denied' error. The error line reads as:
System.Data.SqlClient.SqlException: The SELECT permission was denied on the object 'Contacts', database 'Homer', schema 'dbo'.
Really appreciate any help...
May 9, 2008 at 12:20 am
The user on the database that your published site uses does not have select permissions on the tables. It's probably just got exec on the procs.
Inline SQL (especially if concatenated together and executed) is a really bad idea on web sites, as is a user with permissions on the base tables. It leaves you wide open to SQL injection attacks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 9, 2008 at 12:51 am
Thanks Gail,
I checked the memberships number of times and everything was ticked and I thought OK. However, if I read every item carefully, I would have picked up the problem ages ago. Now I unticked db_denydatareader and it worked. Thanks for pointing out. What I am confused is now why there are 'db_datareader' and 'db_denydatareader' options? same question also holds for db_datawriter. How do you control the access to the stored procedures in role memberships?
Regarding to your recommendation on stored procedures, I use them almost everywhere but only few places is more convenient to use inline code. Also the site is password protected and can only be access by the handful of people who are part of the same small organisation.
Cheers
May 9, 2008 at 12:58 am
Deny overrides grant, so you can use deny to override grants if the user in question is in more than one role.
The built-in roles don't have anything for just exec on procs. What's recomended is that you create a user-defined database role and grant that role exec on all of the proce, or exec on the DBO schema. Then add the users as members of that role.
The principle of minimum permissions means grant just what's needed and nothing more. No db_owner to anyone that doesn't need that level of permissions. No ddl_admin to anyone that doesn't need to create/alter objects.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 10, 2008 at 6:19 am
John Smith (5/9/2008)
Regarding to your recommendation on stored procedures, I use them almost everywhere but only few places is more convenient to use inline code. Also the site is password protected and can only be access by the handful of people who are part of the same small organisation.
Just a word of caution here. If the username/password or just password check is using ad hoc queries (not through stored procedure) or even if it is using procedures but the access to the database doesn't use command objects and parameterization, it could still be hit with a SQL injection attack. Also, if an attacker is able to crawl your site via some means and every page doesn't do some sort of check (I know .NET can handle a lot of this for you, I'm speaking in general), those pages which do use ad hoc queries are potentially at risk.
But with that said, if you're using SQL Server 2005, you can create stored procedure which build the SQL dynamically and then use the EXECUTE AS clause for the stored procedure to ensure the batch runs under a user context that has rights to access the base tables. This prevents giving those rights to the base tables through the normal means.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply