Permissions to Sql2005 database from IIS

[Nemisis]

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?

 

[Goofy]

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.

 

[Yves. L.]

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]

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.

 

[Yves. L.]

Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TEST) 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\TEST]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TEST]



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

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]

Hi,
Assuming you're only using stored procedure your new user (lets call him
LOCALSERVER\TEST) 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\TEST]
GRANT EXECUTE ON [dbo].[GetSp] TO [LOCALSERVER\TEST]



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

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?

 

[Yves. L.]

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 .

 

[Nemisis]

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

 

[Yves. L.]

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.htm

it describes what access users get for those database roles

 

[Nemisis]

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