| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2008-05-06 : 12:50:05
|
| I am creating a login with a user in one database. The id has to create procedures in the dbo schema, execute them, and grant execute on them to other users.BOL says to GRANT ALTER ON SCHEMA::DBO TO username ; GRANT CREATE PROCEDURE TO username ;I did that. User can now create procedures, but cannot execute what he creates. And cannot grant execute on the sp to anybody.I don't want to give this id a lot of priviledges. |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-05-06 : 12:56:47
|
| By the way. This id is a datareader. His function is to create reports using Reporting Services |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 14:29:50
|
| Either give execute permission on SP or Check out db_executor. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-05-06 : 17:22:58
|
| Sodeep,I noticed your comment in a similar thread. You said to check out sp_executor in Books Online. Problem is that it isn't there.So I read some of the internet discussion on sp_executor.Problem: It doesn't exist unless you create it. We have too many applications with too many DBAs to start creating clever one-off solutions unless really necessary.... even if it did what I wanted - which it doesn't seem to.Question 1:If I create a role, put the user in it and execute grants to the role, why not just give those permissions to the user? I'm not going to be doing this for lots of users....just one.Question 2: One web thread suggested GRANT EXECUTE ON SCHEMA::DBO TO DB_EXECUTOR. I looked this command up in Books Online and could not find what is being granted. It gave the syntax and said you could do it. What does it do? What besides procedure execution does it allow? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 17:39:04
|
| I am talking about db_executor not sp_executor. It allows for functions as well. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 20:58:15
|
| You have to do like this for all SP. CREATE ROLE db_executorGRANT EXECUTE TO db_executorexec sp_addrolemember 'db_executor','YourUser'or at schema-levelGRANT EXECUTE on schema::dbo TO db_executor |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-06 : 21:57:10
|
| db_executor is not builtin db role in sql2k5. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 22:47:29
|
| SO? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-06 : 23:03:00
|
| Doesn't solve OP's issue. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-06 : 23:09:33
|
| It has been working for me since 5-6 months. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-06 : 23:18:18
|
| Then you have to tell OP to create db role then grant exec permission to the role. 'give execute permission on SP' is right but not db_executor thing. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-05-07 : 09:27:50
|
| sorry, I misspelled. I meant db_executor, not sp_executor. I still don't get it. Are you saying that after user creates the sp, I have to go grant the execute to the role for him to execute the sp that he created (or run a script to discover new SPs and grant execute) ? The questions are the same.1. There's only one id. Why do grants to a role with one user in it? If I'm going to do grants, why not just grant to the user?2. What does EXECUTE mean in GRANT EXECUTE ON SCHEMA::DBO TO DB_EXECUTOR ? What priviledges besides SP execute does this grant?3. Is there really no way to give this guy the automatic right to execute procedures he creates? How is he going to test ? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-07 : 15:40:24
|
| 1. role is not required.2. it's execution permission only.3. users can grant permission on sp they created. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-05-07 : 16:40:57
|
| We're not quite there.as saI created 2 users, SPCREATOR and SPRUNNER.GRANT ALTER ON SCHEMA::DBO TO SPCREATOR ;GRANT CREATE PROCEDURE TO SPCREATOR ;GRANT EXECUTE ON SCHEMA::DBO TO SPCREATOR WITH GRANT OPTIONNow, SPCREATOR can create an SP (say, SPNAME)and he can now execute SPNAME. So far, so good.So now SPCREATOR executes:GRANT EXECUTE ON OBJECT::DBO.SPNAME TO SPRUNNERand he gets the message:Msg 15151, Level 16, State 1, Line 1Cannot find the object 'SPNAME', because it does not exist or you do not have permission.sa can run that grant to SPRUNNER, but SPCREATOR can't. Sure beats me why. Any ideas? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-05-07 : 16:45:40
|
| When create sp, spcreator needs to specify which schema to hold that sp. Default is its own schema, so have to reference it as schema.sp_name. Or create sp with 'create procedure dbo.sp_name ...', then you can reference it just with sp_name. |
|
|
|
1sabine8
Posting Yak Master
130 Posts |
Posted - 2008-09-05 : 02:25:12
|
| Dear dbthj,I am trying to execute my sp from Reporting Services.The user who created the sp can run the report but the other users get the following error: Cannot find the object 'SPNAME', because it does not exist or you do not have permission.Were you able to solve this error?How? |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2008-09-05 : 16:45:06
|
| Yeah. A lot of my confusion on this came from my failure to realize that sp's, like other objects, all have an owner. when you create it, you have to say who the owner is (otherwise it is owned by the creator). THEN. he has to grant execute on it to whoever. THEN whoever runs it has to qualify it with the owner.Example:user1 is a member of the SPCREATOR role.user1 creates SPCREATOR.xx_spthing.user1 grants execute on SPCREATOR.xx_spthing to public (or a role or a user)user2 runs exec SPCREATOR.xx_spthing.If it ain't qualified, he is real likely to get the"can't find the mother" message. |
|
|
|
|
Grant Create - and execute - Procedures