User Permissions: db_datawriter

[anonim1]

I am using ASP.NET pages with a MSSQL back-end server. I've set everything up so that the ASP.NET pages use integrated authentication for the IUSR_machine account, and I've created this account under SQL Enterprise Manager.

Everything works when it comes to the web application reading data from the database. However, writing data doesn't quite work as planned. If I give the IUSR account db_owner permissions on the database, data can be written to the database without problems.

As soon as I remove this permission (all that's left is public, db_datareader, and db_datawriter), I can't write to the database from the web application.

Here is what's weird.. without db_owner, if I remove the db_datareader permission, I get an error immediately when the web application tries to read from the server. Once I grant db_datareader, the error is gone. However, there are NO errors when it comes to trying to write to the database; the web application does not throw an exception, but no data is written to the database. I'm starting to wonder if the default permissions assigned to the db_datawriter permission are messed up?

Anyone have any ideas?

 

[anonim1]

Okay, so I figured out that what I want to be able to do is globally give a user account access to all stored procedures, tables, views, etc. in a database. Is this the same as assigning that user dbo permissions on that database? I don't want to give more permissions than are needed, but I also don't want to have to go through every single table, every single stored proc, etc. to give access.

Thanks.

 

[mrdenny]

The only way to automatically grant rights to all procedures is by granting dbo rights.

I recommend writting a script that will loop through all the objects and grant the exec rights to a user.

I use something like this.

declare @ProcName as varchar(255)
declare cur CURSOR for select name from sysobjects where xtype = 'P' and Category =  0
open cur
fetch next from cur into @ProcName
while @@FETCH_STATUS = 0
BEGIN
	exec ('grant exec on ' + @ProcName + ' to UserName')
	fetch next from cur into @ProcName
END
close cur
deallocate cur

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief