| Author |
Topic |
|
jhermiz
3564 Posts |
 Posted - 2004-11-04 : 15:01:42
|
| I have a database with a user IMS...after reading and reading I read it was best to create another user for this db simply for reporting services to report on the data. I gave this user the user name rs and gave it db_datareader as the role.Even the book recommends this....So when I run the sproc that I created in vs.net (or use it as a data set) and try to run it I get a message about:An error occurred while executing the query. EXECUTe permission denied on object 'select_rpt_user_profile', database 'IMS', owner 'dbo'.How do I get this to run with this "rs" user ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 15:05:36
|
| Well the user is also to need execute permissions on your stored procedures. db_datareader is only so that they can do selects on tables. Even if the stored procedure only does selects, granting db_datareader or select permission on the table is not enough. The user must have exec on the stored procedures. I would remove the user from the db_datareader role, then GRANT EXEC on all of your stored procedures that this user will need to execute for a report.Tara |
 |
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 15:45:03
|
| hmmm....So does this mean this user will not be part of db_datareader role ? What role should I give this user ?I want to be able to call views and sprocs using one generic user. Didnt want to use a system admin account. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 16:02:52
|
| You would create a role, add your account to the role, then grant privileges to the role, whatever privileges are needed. This is how your app should be doing it too, btw. You'll definitely need exec on sproc. When you call views, are you doing that from within a sproc or directly? And no you won't need a system admin account.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:06:35
|
| Ok I honestly dont know this...but create a role ? I know you're not talking about a server role but where do you create a general role ? Cant I just assign this user in the public role and specify the rights needed there ? |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:07:48
|
| Nevermind I thought you were referring to a server role duhhhhhhhhI really need some coffee |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 16:09:30
|
| Role, I'm talking about database roles. I certainly wouldn't use public role. sp_addrole - to create a rolesp_addrolemember - to add the user to the roleThen grant the permissions to the role using GRANT statement. You can also do all of this inside EM, just go to the database, then roles, create your role, then add your user to that role. Then grant permissions. How do you normally handle security?Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:10:21
|
| Im sorry I needed coffee...I've got it configured now.Been a rough week tara .. go easy :-p on me hahahaha |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:12:36
|
| I have a question though...why is it that if I look at public role it just shows check marks next to system objects...but if I have a user on this role he / she can still execute stored procedures and stuff ?For this new user "rs" in the new role "db_reporter" should I be generally checking pretty much every table AND exec permissions for all my stored procedures that this user will need access to ?Seems a bit tedious but I guess that's following the right ways of doing this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 16:15:44
|
| That's how the public role should be. I'd have to see the list of permissions for the user and role to answer the second part. I never check everything. I only grant what the user will need, which in your case will be the small set of stored procedures that it will call from RS.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:16:35
|
| Ok I assume if you mark off the procedure since the procedure is doing a select on the table then I dont need to check off "SELECT" for the table itself |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 16:17:08
|
| This might help you decide which stored procedures to grant permissions to...For the project that I am working on where we are using Reporting Services, we decided to prefix the report stored procedures name with rsp_. Then we only grant exec on the rsp_ to the role. This also let's you distinguish them from other sprocs when you see them in a list.Tara |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-04 : 16:18:07
|
quote:
Originally posted by jhermiz
Ok I assume if you mark off the procedure since the procedure is doing a select on the table then I dont need to check off "SELECT" for the table itself
That's correct, UNLESS you are using any dynamic SQL in the stored procedure. If you are, then explicit permissions on the table are needed. This is why dynamic SQL should be avoided as granting explicit permissions onthe tables is a bad security thing.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:18:10
|
| Definately good idea, I was doing select_rptBut I can see how your method is much simplier.Thanks for the idea.Jon |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-11-04 : 16:18:41
|
| Great Tara That makes sense regarding the permissions.Thank You so much.Jon |
|
|
|
|
rs user ?