December 18, 2017 at 1:31 am
Hi,
Our staff members all have only db_datareader permissions and View definition on all production databases.
But many times they require to execute a stored procedure which in essence will only return data (no UPDATE, INSERT, DELETE statements).
I know I can grant execute to individual procs but this could become an admin nightmare - because we have 100's of procs and 100's of databases.
I was hoping someone had a clever way of finding all procs that will only return a dataset (no data changes), and then grant execute to all staff for these specific procs?
Thanks
December 18, 2017 at 2:02 am
Not sure you can no.
Personally, I would create a role, add all the users to the role and then grant the relevant Execute permissions to that role on all your SP's that you need. Yes, initially it's going to take awhile, but after that it's just a case of adding any new users to the role.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 18, 2017 at 2:37 am
Another alternative, if it's an option for you, is to create a new schema. Call it something like ReadOnlyProcs. Put all of the stored procedures you describe into that schema and grant the users, or better still the role of which they are members, EXECUTE permission on the schema. Beware, though - if you have an application that calls these stored procedures then some recoding may be necessary.
John
December 18, 2017 at 2:55 am
Its not 100% guaranteed, but you can look through sys.sql_modules in each databases, query where the definition is LIKE SELECT and NOT LIKE INSERT UPDATE DELETE.
As I say its not 100% as if you have a read proc which generates a temporary table then you exclude that proc also as you have filtered out INSERT INTO #~~~~~~~
SELECT 'GRANT EXECUTE ON ['+OBJECT_SCHEMA_NAME(object_id)+'],['+OBJECT_NAME(object_id)+'] TO [<<YourRole/Login>>];' FROM sys.sql_modules WHERE definition LIKE '%SELECT%' AND (definition NOT LIKE '%INSERT%' OR definition NOT LIKE '%UPDATE%' OR definition NOT LIKE '%DELETE%')
Copy the output of the above into a new query window then run it.
December 18, 2017 at 3:09 am
anthony.green - Monday, December 18, 2017 2:55 AMIts not 100% guaranteed, but you can look through sys.sql_modules in each databases, query where the definition is LIKE SELECT and NOT LIKE INSERT UPDATE DELETE.As I say its not 100% as if you have a read proc which generates a temporary table then you exclude that proc also as you have filtered out INSERT INTO #~~~~~~~
SELECT 'GRANT EXECUTE ON ['+OBJECT_SCHEMA_NAME(object_id)+'],['+OBJECT_NAME(object_id)+'] TO [<<YourRole/Login>>];' FROM sys.sql_modules WHERE definition LIKE '%SELECT%' AND (definition NOT LIKE '%INSERT%' OR definition NOT LIKE '%UPDATE%' OR definition NOT LIKE '%DELETE%')Copy the output of the above into a new query window then run it.
Get's you most of the way there. Would also not work if you have any objects with a name containing the words INSERT, UPDATE, or DELETE. I can imagine columns containing UPDATE being more common than any. Decent start, hopefuly, for the OP though. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 18, 2017 at 6:40 am
What about creating a role, grant execution to the role, assign the users to the role. You manage security for the procs only with the role. You manage user permissions only by inclusion within the role. It won't make this zero work, but it should make it radically less work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply