December 23, 2009 at 10:49 am
HI,
I did this all manually via MS SQL management studio. My Asp.net project only uses store proces to communicate with DB.
1)I created a Database role on my DB - called it 'OPERATOR'. Its is the owner of 'dbo' [is that ok??]
2) I added to this role all my stored proc and gave them EXECUTE permissions. I didnt add any schemas.I didnt select 'With Grant' or 'Deny'
3) I also added to this DB role in (1) above all inline and scalar functions, and granted EXE permissions. But I feel I didnt need to as all these are either in a stored proc or a database computed colomn. Please advise here.
4) I created a user to the server instance called it 'DBUser', I mapped this user to my DB, and I gave it a default scheme 'dbo' [is this part correct, 'dbo'???]
5) I then went back my DB at selected the database role I created called "OPERATOR" and added 'DBUser' to its membership.
I changed my connection string on my asp.net project to have a user id and password wth TRUSTED_Connection set to false.
All seams to work ok.
Please adress my issue above thanks
December 26, 2009 at 9:04 am
That should be fine, though a couple notes.
1. Should be OK, you didn't add the dbo role to this role, correct?
2. You add permissions to the role, you don't add the role to the stored procedures. I realize you may mean that you added the grant to the script, and that's what I recommend, but you are adding EXECUTE permissions to the role on the object.
3. This is probably not necessary. However, since you could call a function sometime outside of a proc, this doesn't hurt. Just like you don't need to grant permissions to a table for a stored procedure to select from it, you don't need to for functions. If they have the same owner, the security passes through.
4. default schema is up to you. I use dbo, avoid other schemas in general unless its a multi-application db.
5. correct.
Kudos for using stored procs for access. Was it hard to set up (in your mind)?
December 26, 2009 at 12:00 pm
Ok thanks.. you have confused me on (2)..
I have a role..I selected the StoreProc, pushed it to the role, to select EXECUTE.
So the role as the permission to execute the stored proc. I didnt select WITH GRANT or DENY. So am I sweet.
NOTE: No wear did I select db_datareader or db_datawriter for either the role or the user setup.
still ok ???:-)
December 26, 2009 at 12:21 pm
Selecting the execute permission in SSMS is the same as executing the GRANT in T-SQL. You should be fine.
The permissions are in the role, not the stored procedure.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply