473,574 Members | 2,267 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Permissions to Sql2005 database from IIS

Hi everyone, hope your all looking forward to xmas.

I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.

My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.

I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.

I do not want to use Sql Authentication.

How should i setup permissions on my database?

At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.

Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?

Dec 19 '06 #1
9 1829
IM new to SQL 2005 as well, but Im working through the implementation and
Maintainence book and it expplicitly tells you not to user the Network
Service account because it has too many privilages. It reccomends setting up
a new account to manage each instance.
"Nemisis" <da*********@ho tmail.comwrote in message
news:11******** **************@ a3g2000cwd.goog legroups.com...
Hi everyone, hope your all looking forward to xmas.

I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.

My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.

I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.

I do not want to use Sql Authentication.

How should i setup permissions on my database?

At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.

Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?

Dec 19 '06 #2
Hi nemesis.

Is you database server & Web Server located on the same machine?

If yes
You could create a local (technical) user account & have the application
run under an application pool with the technical user account.
On the DB level , just create the login & user account and grant execute
on the stored procedure.
If No
instead of a local (technical) user account have a domain account made.
the rest of the setup is just the same.
As a side note , never have any application account part of db_owner ,
this means the app. could just drop all tables in your DB .
Yvesl

Nemisis wrote:
Nemisis wrote:
Hi everyone, hope your all looking forward to xmas.

I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.

My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.

I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.

I do not want to use Sql Authentication.

How should i setup permissions on my database?

At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.

Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?

Hi everyone, hope your all looking forward to xmas.

I am setting up a Sql2005 database on a Windows Server, running Windows
Server 2003. The database is going to be accessed via users using an
ASP.Net website.

My website is running the normal IUSR account. And users must login to
the website, which uses Forms Authentication.

I do not want to give users access directly to tables, so have created
many stored procedures which will carry out the operations that i would
like users to be able to do.

I do not want to use Sql Authentication.

How should i setup permissions on my database?

At the moment i have set up the NETWORK SERVICE account (account for
running asp.net) on my server to be a role member of the db_owner group
for the database. This allows the user access to all the object in my
database, as everything is owned by dbo.

Is this correct? This doesnt sound right to me? Do i have to setup
another user in my Sql database?
Dec 19 '06 #3

Thanks alot for this, my database server and web server are on the same
machine.

I will try to setup a account and set the correct permission first
thing in the morning.

The new account that i will setup, does it need access to the tables?
Because before when i tried to execute a stored procedure, it said that
select permissions had to be set on the table as well. Is this still
the case??

Should the new account be apart of a role in the database? Or should i
created my own?

Thanks again for your help so far.

Dec 19 '06 #4
Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TES T) does not need any access to the tables.
It needs EXECUTE right to the stored procedures.

here's the script to grant access.
GRANT EXECUTE ON [dbo].[AddSp] TO [LOCALSERVER\TES T]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TES T]

For Role Membership, well it all depends on what your requirements are.
For more informed help you should probably ask this newsgroup:
microsoft.publi c.sqlserver.ser ver

however my 2€c:
Since your Database will be accessed only through 1 user account( the
one that your web app. runs under) I don't think it's necessary to
create any Role.

YvesL

Nemisis wrote:
Thanks alot for this, my database server and web server are on the same
machine.

I will try to setup a account and set the correct permission first
thing in the morning.

The new account that i will setup, does it need access to the tables?
Because before when i tried to execute a stored procedure, it said that
select permissions had to be set on the table as well. Is this still
the case??

Should the new account be apart of a role in the database? Or should i
created my own?

Thanks again for your help so far.
Dec 19 '06 #5

Yves. L. wrote:
Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TES T) does not need any access to the tables.
It needs EXECUTE right to the stored procedures.

here's the script to grant access.
GRANT EXECUTE ON [dbo].[AddSp] TO [LOCALSERVER\TES T]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TES T]

For Role Membership, well it all depends on what your requirements are.
For more informed help you should probably ask this newsgroup:
microsoft.publi c.sqlserver.ser ver

however my 2€c:
Since your Database will be accessed only through 1 user account( the
one that your web app. runs under) I don't think it's necessary to
create any Role.

YvesL
Yves,

What about if i need to run dynamic Sql? Will this still be ok? I
hear that there are some problems when running dynamic Sql?

Dec 20 '06 #6
>
Yves,

What about if i need to run dynamic Sql? Will this still be ok? I
hear that there are some problems when running dynamic Sql?
I suppose that you mean SQL statements in code ?
Then you'll need to set the proper permission on the necessary tables,
views, etc,... or make your user part of the db_datawriter or
db_datareader roles (see the docs for reference).

Anyway, you wrote earlier that you have SP'for all data access, so you
should be fine .
--
Regards,
YvesL
Dec 20 '06 #7
Yes the users only execute stored procedures, but within some of the
stored procedures, i execute dynamic Sql, using the execute_sql system
stored procedure.

I was told that it was not a good idea to make a user apart of the
db_owner role, is making them a part of the db_datareader and
db_datawriter role ok?

I am using Sql2005, so does this still apply? I didnt know if i should
do something different for Sql2005?

Thanks

Dec 20 '06 #8
Yes the users only execute stored procedures, but within some of the
stored procedures, i execute dynamic Sql, using the execute_sql system
stored procedure.
I don't know about that, you'll have to try it out or ask the sqlserver
newsgroup.

>
I was told that it was not a good idea to make a user apart of the
db_owner role, is making them a part of the db_datareader and
db_datawriter role ok?

Have a look at
ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/udb9/html/7f3fa5f6-6b50-43bb-9047-1544ade55e39.ht m

it describes what access users get for those database roles

--
Regards,
YvesL
Dec 20 '06 #9

Hope u all had a merry xmas, and happy new year too you all.

I have followed the steps in the article
http://www.sommarskog.se/grantperm.html.

1. Create a certificate
2. Create a user associated with that certificate
3. Grant that user SELECT rights on the table
4. Sign the procedure with the certificate EACH TIME you have changed
the procedure

Encase you dont know, i have had to create this certificate as i am
executing dynamic Sql within some of my stored procedures, and i dont
want to give the user any permissions to the tables.

I am a lil confused, as i have to create a user (above) to associate
with the certificate, and i have to give that user permissions to the
table directly. Would it not have been easier just to give the main
user (web application IIS user, NETWORK SERVICE) direct permissions to
the tables, instead of setting another user up?

Could someone please clarify why this is the case and the benefits of
it, i have to also inform other people why we are going to set the
application up like this.

Thanks

Jan 2 '07 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1793
by: ad | last post by:
The user of my asp.net program use Sql2005 or Sql2005 express as database, both are possible. I have try both version of data base, I found: If I use Sql2005 express, the Data Source in connection string will be ..\SQLEXPRESS, like: Data Source=.\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True But I use Sql2005 , the Data...
0
1116
by: Peter Newman | last post by:
This has already been posted but in the wrong section. im very new to visual basic .net and have been trying to modify some of my 'amauter' visual basic applications under vb.net. I am currently working on an application that runs a DTS on a SQL1005 server. There is nothing to parm to the DTS it just needs to be fired off. I found some...
5
3898
by: Mike | last post by:
I'm having trouble accessing SQL2005 Standard Edition as a second instance of SQL Server where the first instance is SQL 2000 Enterprise Edition. I installed SQL 2005 as a named instance "SQL2005". The server is running Windows 2000 SP4 ON A 32-Bit machine. When I look in Services I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)...
8
1499
by: Radu | last post by:
Hi. This code worked fine with SQL2005, but has a problem with SQL2000.... Which one ???? cnSQL = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("ConnectionString")) cnSQL.Open() fails with the error "Format of the initialization string does not conform to specification
7
1284
by: Earl | last post by:
Can anyone point me to some good references for coding a Winforms app to trigger replication (merge preferably) on SQL2005? I'm setting up a scenario where I want my app to allow multiple laptops to synchronize to the distribution SQL2005 database and I'd like to do it either automatically or by having them click on a menu/button. From working...
3
2964
by: Tawfiq | last post by:
Hi, We have shifted one of our data processing to a new instance of SQL 2005. For this a new DB was created in SQL2005 and it is not an upgrade of SQL200 DB. The data processing application is a VB6 application that runs a batch process to insert about 4.5 million records everyday in this SQL 2005 database. I am using ADO
2
1109
by: Ryan Langton | last post by:
I've created quite a few .Net apps we use locally and I generally just publish them as ClickOnce applications to a network drive. Now I'm being asked to make my app portable to other networks/companies. So I want to create a setup program on CD/DVD. The hurdle here is that my apps are often database driven and use SQL2005 as the backend. ...
3
2886
by: Michael Schöller | last post by:
Hello, First of all english is not my natural language so please fogive me some bad mistakes in gramatic and use of some vocables :). I have a great problem here. Well I will not use it anymore but I want to know why it is as it is ^^. I tried with .NET3.0 but I think it will be the same with 2.0 and 3.5. MSDTC is configured and working.
0
1995
by: nic829 | last post by:
Hi, I was upgrading a instance from SQL2000 to SQL2005. During SQL2005 setup I accidently allow the files to be installed in C drive, but I should have them all installed in D drive. My databases are still sits in D drive, but other files (Management studio, analysis service, etc.) all sits in C drive. Is there anyway that I can move SQL2005...
0
7747
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8093
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7843
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8129
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6498
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5643
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5331
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3768
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1364
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.