| Author |
Topic |
|
rjackman1959
Yak Posting Veteran
60 Posts |
 Posted - 2009-01-18 : 12:51:52
|
| I need to give a non admin user access to create, alter and drop a temp table. If it matters this procedure call will be from within an Excel VB script. Is there a way to give them acces to only this table or procedure within the procedure, but not the whole database? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-18 : 12:56:17
|
| yup. it possible. you can do this either from sql mgmnt studio by expanding table/stored procedure and granting permissions. or use GRANT... T-sql command. |
 |
|
|
rjackman1959
Yak Posting Veteran
60 Posts |
Posted - 2009-01-18 : 13:01:31
|
| Thanks visakhI thought I cound do it in the properties of SQL, but I still get a CREATE TABLE permision denied in database error when the user tries to use it. Can you give me an example of the GRANT command or is there somewhere else I need to look to allow him access? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-18 : 13:08:28
|
quote:
Originally posted by rjackman1959
I need to give a non admin user access to create, alter and drop a temp table. All user with public role should have this permission.If it matters this procedure call will be from within an Excel VB script. Is there a way to give them acces to only this table or procedure within the procedure, but not the whole database?Do you mean execute SP permission?
|
|
|
|
rjackman1959
Yak Posting Veteran
60 Posts |
Posted - 2009-01-18 : 13:18:07
|
| Yes, but only this one procedure and only certain users. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-18 : 13:20:08
|
| Right click that procedure-Properties-Permission and Give Execute Permission to particular user. |
|
|
|
rjackman1959
Yak Posting Veteran
60 Posts |
Posted - 2009-01-18 : 13:26:46
|
| I actually did that, but I am still getting the same error. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-18 : 13:28:45
|
| You can't let user create user table with permission. |
|
|
|
rjackman1959
Yak Posting Veteran
60 Posts |
Posted - 2009-01-18 : 13:37:13
|
| Can I use a grant statement within the procedure to a user or group? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-18 : 13:50:28
|
| You need to give them Create table permission? |
|
|
|
rjackman1959
Yak Posting Veteran
60 Posts |
Posted - 2009-01-18 : 14:40:01
|
| Sorry for my ignorance, but can this be done temporarily while the user is accessing the procedure? If not can you tell me where in studio I give them create and drop access? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-18 : 15:27:47
|
| With this:http://msdn.microsoft.com/en-us/library/ms178569.aspx |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
|
|
|
Temp create and drop access to users