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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-01-17 : 09:15:24
|
| Tim writes "Our site uses all stored procedures, and we changed IIS to use webuser as the working account. We granted access in the stored procedure for webuser via: grant execute on spname to webuser The connection string uses trusted_connection=yes so IIS definitely uses that user. We gave the user no server role, and on the database access role, we gave it public, datareader, and datawriter. What is required for it to work? I know setting it to dbowner would work (since the stored proc is a dbo.spname) however, we were trying to avoid that. What permission is needed on the user for execute? And, if we don't 'grant execute ...' then do we have to set the user up as dbowner? Thanks for the help!" |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-17 : 09:54:44
|
| I create a server role called DB_APPLICATION and then for each database there are users that connect to the server as a DB_APPLICATION and have access to specific databases ... and each database grants DB_APPLICATION the proper permissions required to run...-- noteby the way, if you don't want to have to GRANT EXECUTE on all the stored procedures manually you can do thisSELECT 'GRANT EXECUTE ON [' + ROUTINE_NAME + '] TO DB_APPLICATION' FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'Edited by - onamuji on 01/17/2003 09:56:13 |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-17 : 13:05:22
|
| You definitely do not have to set the user up as dbowner. That is not recommended at all. You have to grant execute in order for the user or role to execute stored procedures. So just create a role, add the user to the role, then grant execute on all stored procedures using Onamuji's code (you will have to copy the output into a new window and then execute the output). |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2003-01-17 : 14:00:25
|
| Just to reiterate the previous replies, if all access is via stored procedures and assuming no dynamic sql in those procedures then the user account/user database role does not need anything other than public role member ship . They do not need any access to the underlying tables assuming all objects are owned by dbo and ownership chains are intact. They definately do not need to be in the db_datareader or db_datawriter fixed database roles.HTHJasper Smith |
|
|
|
|
|
|
sql server roles