EXEC Only from IIS Web Site - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

EXEC Only from IIS Web Site

Author

Topic

gburgur
Starting Member

2 Posts

Posted - 2012-05-05 : 08:12:46

Windows Server 2008
SQL Server 2008

Currently, the web site is calling stored procedures through the Administrator's account. (I know that is terrible and that is what this question is about.) Obviously the web sites uses a connection string (it is encrypted in the web.config) to run the stored procedures but I am afraid that one day, someone will see the UNencrypted connection string who is not supposed to see it and have Admin access to the database.

I want to create a user in SQL Server 2008 with ONLY execute permissions on the stored procedures. The SPs call functions, perform updates, inserts, etc. and other SPs.

What permissions do I give from Windows Server 2008?
I do not want to over grant "exec stored procedure" access to the system.
I see:
SQLServer2005SQLBrowserUser
SQLServerMSSQLUser
IIS_IUSRS

Thanks,
Gene

Sachin.Nand

2937 Posts

Posted - 2012-05-05 : 12:16:18

Create a role,add the users to whom you want to grant the execute permission and use dynamic sql to grant execute permissions to that role.Something like this

select 'grant execute on ' +  
    specific_name + ' to rolename' 
from information_schema.routines 
where routine_type='PROCEDURE'

After Monday and Tuesday even the calendar says W T F ....

gburgur
Starting Member

2 Posts

Posted - 2012-05-05 : 16:46:52

That would have been my next question. :)

But my current question is for Windows Server 2008.
What permissions do I give from Windows Server 2008?
SQLServer2005SQLBrowserUser
SQLServerMSSQLUser
IIS_IUSRS

Thanks,
Gene

Sachin.Nand

2937 Posts

Posted - 2012-05-06 : 03:17:16

Do not use windows login in any kind of authentication in your website which I personally feel is a very bad practice if you do so.

Create a sql login and follow the steps which I have mentioned in my previous post.

After Monday and Tuesday even the calendar says W T F ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources