SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 EXEC Only from IIS Web Site		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gburgur
Starting Member

USA
2 Posts
Posted - 05/05/2012 :  08:12:46  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2937 Posts
Posted - 05/05/2012 :  12:16:18  Show Profile  Reply with Quote
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 ....
Go to Top of Page

gburgur
Starting Member

USA
2 Posts
Posted - 05/05/2012 :  16:46:52  Show Profile  Reply with Quote
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
Go to Top of Page

Sachin.Nand
Flowing Fount of Yak Knowledge

2937 Posts
Posted - 05/06/2012 :  03:17:16  Show Profile  Reply with Quote
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 ....
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000