February 4, 2011 at 8:29 am
I have a user with db_datareader access only to a database. They are requesting access to run stored procedures and functions in the database, but their account is restricted... How or what should I do to grant this access WITHOUT giving them UPDATE, DELETE, CREATE, DROP, etc.
February 4, 2011 at 10:35 am
You can grant the EXECUTE permission on each stored procedure that you want the user to be able to run. If you do that, the user won't need the explicit DML permission on tables.
grant execute on [i]procedure[/i] to [i]user[/i]
Greg
February 4, 2011 at 2:58 pm
If you've got a lot of stored procedures to update then this might of of use to you. It's certainly made it easier for me in the past! As you'll see it assumes that you have a standard naming convention of your stored procedures.
DECLARE curSPs CURSOR FOR
-- adjust sp% to something unique to your SP names
SELECT name FROM sysobjects WHERE name LIKE 'sp%' AND type ='P'
DECLARE @Name VARCHAR(255)
OPEN curSPs
FETCH NEXT FROM curSPs INTO @Name
WHILE @@FETCH_STATUS = 0 BEGIN
-- adjust myuser to the username you want to give exec permissions to
EXEC('GRANT EXECUTE ON [' + @Name + '] TO [myuser]')
FETCH NEXT FROM curSPs INTO @Name
END
CLOSE curSPs
DEALLOCATE curSPs
Go
February 4, 2011 at 10:18 pm
If you want to grant execute to all stored procedures in a particular schema, you can do the following:
GRANT EXECUTE ON SCHEMA::{schema} TO {user/role};
Advantage to this is new procedures created will also be available to that user/role. Disadvantage is that this applies across the whole schema.
If there is a subset of procedures, you could create a new schema - then create synonyms for that subset of procedures in the new schema and grant execute to the new schema. Again, as new procedures are created (or synonyms) in that schema - that user/role will already have rights to execute the procedure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 12, 2011 at 9:48 am
I keep getting the same message either through your suggestion or the cursor.
Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'RptWriter', because it does not exist or you do not have permission.
What am I doing wrong?
February 12, 2011 at 10:02 am
Never mind... I figured it out. I was in the wrong database (master) when I needed to be in the actual database. :w00t:
Thank you for the help.
February 12, 2011 at 10:10 am
This worked just fine. Thank you
Now to continue my "Pain in the butt" questions, what can I do for this same account to "modify" functions?
The reason for the user role change was remain in compliance with company policy to not let users update, delete, insert, etc. data the database. However, the user still needs access to stored procedures (which now solved) and functions.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply