Assign execute permission to all existing and new sp's for application role automatically

Question

Assign execute permission to all existing and new sp's for application role automatically

E McNeill

SSC Veteran

Points: 277
More actions
June 14, 2016 at 11:14 am

#308355

I'm getting ready to create an sql server database that will be access by a visual basic windows application. I've created an application role that the program will connect with. Before I start creating my stored procedures and other database objects is there a way to insure that my application role will be automatically assigned execute permission for all new stored procedures I create, as well as insert, delete, and update permissions for all m tables.
I'm using Sql Server2014 and know how to set those permissions manually but it would be nice to be able to set these permissions automatically for new objects.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

create a role, and then grant execute to the role; don''t grant on a per object basis, and you'll be fine.

as long as the proc uses objects only in the same database and schema, underlying permissions to objects are not required...object ownership chaining takes care of that, so execute is enough.

if you want them to have permissions to the underlying tables for SELECT INSERT UPDATE DELETE,those permissions are separate grants, or from existing roles like db_datareader and db_datawriter

-- Create a db_executor role

CREATE ROLE db_executor

-- Grant execute rights to the new role

GRANT EXECUTE TO db_executor

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

E McNeill SSC Veteran Points: 277 More actions · Answer 2

I had already did something similar to that on the application role I created.

I create an application role using Sql Server management called FileWatcher then executed the code :

GRANT EXECUTE TO FileWatcher

Than didn't give me the permissions I needed either. I also tried your code and that didn't work either.

-- Create a db_executor role

CREATE ROLE db_executor

-- Grant execute rights to the new role

GRANT EXECUTE TO db_executor

I refreshed all of the objects after running it and even disconnected then reconnected. It looks like your code created a database role called db_executor. Should I be using a database role or an application role?

I'm checking permissions by right clicking on a stored procedure in the object explorer and viewing properties and permissions. Execute permission is not assigned to any user.

Is the code you gave me effect all procedures or only newly created procedures? I had created a test procedure just so I could check this code.

I'm using the express edition of Sql Server 2014

durai nagarajan SSCoach Points: 15775 More actions · Answer 3

have you mapped your application user to the created role.

express edition are not meant for production.

Regards
Durai Nagarajan

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 4

durai nagarajan (6/15/2016)
have you mapped your application user to the created role.
express edition are not meant for production.

Why would you not use it in production? I know many clients that do when they don't need any database over 10GB. There are application products that use Express Edition and the backend to just store configuration states...it is more than capable of handling production use cases, albeit very small use cases.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

durai nagarajan SSCoach Points: 15775 More actions · Answer 5

durai nagarajan

SSCoach

Points: 15775

June 16, 2016 at 5:12 am

#1885346

thanks, i was wrong

Regards
Durai Nagarajan