March 19, 2014 at 10:50 pm
Comments posted to this topic are about the item Grant EXEC on all Stored Procedures to a Role
March 19, 2014 at 11:12 pm
One of the challenges with assigning permissions to stored procedures is the fact that SQL Server has no inherent way of knowing whether a stored procedure is effectively read-only or one that modifies table data. With tables and views the db_datareader and db_datawriter roles can make this distinction. At our company I have two complementary policies. 1) All stored procedure (view, function, etc.) code is maintained in source-controlled files. 2) Every stored procedure script has a special GRANT at the end: either "GRANT EXECUTE ON <PROC> TO DeveloperRead" or "GRANT EXECUTE ON <PROC> TO DeveloperWrite", based on their development time understanding of the stored procedure's behavior. In QA, the QA testers are in DeveloperRead and DeveloperWrite so they can modify their test data to their heart's content. In production, developers are only in DeveloperRead, allowing them to review stored procedure output with production data when debugging production apps. If they really need to use a "write" stored procedure, I can temporarily put them in DeveloperWrite, and back out when they are done. It has worked well for us.
Sincerely,
Daniel
March 20, 2014 at 4:13 am
This is nice. However, over time I have also started using another approach where we can declare a nvarchar(max) variable at the beginning of the script and then append it as follows:
declare @dynamicSQL nvarchar(max) = ''
select @dynamicSQL += 'grant execute.....
'
from ....
execute sp_executeSQL @dynamicSQL
This way, we can get away with cursor altogether and achieve the same result with the execution of dynamic sql.
Your views?
March 20, 2014 at 9:17 am
Something like this for example:
DECLARE @query table (num int, dSQL varchar(2000))
DECLARE @dsql varchar(2000)
DECLARE @RoleName sysname = 'LoneWolfDBA'
DECLARE @cnt int = 0
INSERT @query (num,dSQL)
SELECT row_number() over (order by ROUTINE_SCHEMA, ROUTINE_NAME) num,'GRANT EXEC ON [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + '] TO [' + @RoleName + '];'
FROM INFORMATION_SCHEMA.Routines
WHERE ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
SET @cnt = @@ROWCOUNT
WHILE @cnt > 0
BEGIN
SELECT @dsql = dSQL FROM @query WHERE num = @cnt
EXEC (@dSQL)
SET @cnt = @cnt -1
END
Since I began reading Itzik Ben-Gan, I try to avoid cursors unless I have a very good reason for using them.
March 20, 2014 at 9:41 am
If we don't want any row level control, then what I was thinking was much simpler than this, actually.
It is something like:
DECLARE @SQL NVARCHAR(3000) = ''
DECLARE @RoleName NVARCHAR(100)
SELECT @RoleName = 'Test_Role'
SELECT @SQL += 'GRANT EXEC ON [' + ROUTINE_SCHEMA + '].[' + ROUTINE_NAME + '] TO [' + @RoleName + '];
'
FROM INFORMATION_SCHEMA.Routines
WHERE ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME NOT LIKE 'dt[_]%'
execute sp_executeSQL @SQL
GO
March 20, 2014 at 9:50 am
In my case, with your solution of packing it all into a variable, my list was truncated because of the limited space. I will try it with a larger variable and see if that improves my results. It is a very good solution for a shorter result set though.
March 20, 2014 at 11:40 am
If your version of SQL Server permits, why not just try nvarchar(max)?
March 20, 2014 at 12:07 pm
That was my final solution, though varchar (or nvarchar) (max) is another SQL feature I hesitate to use, though in this situation it is benign and a good solution.
March 20, 2014 at 12:12 pm
I've run into issues before trying to concatenate a large string and EXEC that using sp_executesql. It's definitely a solution for some people, but at least one time I tried to use it my job started failing repeatedly. Using the cursor worked well to get around that problem, even if it is a row-by-row solution.
As for the WHILE loop, that's really just taking out the CURSOR part and replacing it with something that's not quite a cursor but still goes row-by-row. I don't have a problem using WHILE loops, but to say things like "cursors are bad - use a while loop instead" never struck me as a good solution. Cursors can work well when used properly. In this case, this should hopefully be a one-time solution. I wouldn't tend to advocate this as a nightly job to run for permissions. You should switch over to DB source control of some sort if you're trying to manage permissions better.
Another advantage of the cursor is I can easily just see to which objects I'm about to grant permissions by just running the select portion of the cursor. The dynamic SQL and string concatenation tends to muddy that a little bit.
Use what works for you. I wanted to get the general idea of how you might do this so people can experiment. In my practice, we tend to mostly grant permissions at the Schema level with more granular permissions given as needed. Of course, most of our systems run through service accounts so we don't have a need for more granular control in most cases. If we needed more granular permissions, I'd likely generate them all in some script format, then store those in our SQL Projects so they could be re-used. I'd still grant to a role and then manage role membership through post-deploy scripts. That would be a little more work to set up initially, but I'd not have to touch those for future releases. 🙂
(I should note that I also miss the easy way we used to be able to do this in 6.5 and prior - open permissions, select entire column, grant, save/close, done 🙂 )
March 20, 2014 at 12:24 pm
Please don't misunderstand me, I did not mean to imply CURSORs are bad. I used to use them all the time (yes in SQL 6.5,7.0, 8.0 & 9.0, less in 10.5), I simply try to find ways around it, sometimes there aren't any ways around it. I have nothing against a DBA knowingly using a declared cursor as a tool in a case by case basis. In my youth I made the mistake of teaching an engineer at a telcom I worked for how to use them to make reports, that was a mistake.
I am sure I will get grief for my stance on certain "rules" I have, as you will for suggesting cursors, but as long as we "do no harm" let's just get the job done.
March 20, 2014 at 12:37 pm
Regarding cursors: One of my DBAs had starting writing WHILE loops to avoid cursors. I was enhancing some of her code like this, and I started to optimize the way the loop handled the data. Then I asked the question that hadn't been asked before: Is this really better than a cursor? Wouldn't the MS SQL team have put in a lot of deep-inside optimizations for cursors? I rewrote it as a STATIC, FORWARD ONLY cursor, and it ran much faster. If it just can't be done with set based statements, then use a cursor; don't code some other loop a cursor can do.
Sincerely,
Daniel
March 20, 2014 at 4:14 pm
JediSQL (3/20/2014)
Regarding cursors: One of my DBAs had starting writing WHILE loops to avoid cursors. I was enhancing some of her code like this, and I started to optimize the way the loop handled the data. Then I asked the question that hadn't been asked before: Is this really better than a cursor? Wouldn't the MS SQL team have put in a lot of deep-inside optimizations for cursors? I rewrote it as a STATIC, FORWARD ONLY cursor, and it ran much faster. If it just can't be done with set based statements, then use a cursor; don't code some other loop a cursor can do.
That's kind of the point I was making there, though there may be times that the WHILE loop makes more sense (really large datasets, working in smaller sets but more than one row at a time, etc.). For something small like this, I found the cursor worked well. There are definitely other ways to handle it; this is just the method I chose this particular time. Being able to easily tweak the WHERE clause to see the results has a lot of value to me.
March 20, 2014 at 10:33 pm
I just think its great you took time to write the article and help out, and get people thinking of ways to make their jobs easier. I worked with a guy once when I was still pretty new and he told me, if you have to do something more then once, find a way to script it out.
March 20, 2014 at 11:58 pm
My reflection on discussions such as these is been very practical. At the risk of sounding philosophical, i might voice it anyway.
Language features (be it any language) are like tools as we use them in our day-to-day life, such as knife or fire. We have been taught to put them to good use. We also know these can be misused and cause harm.
Furthermore, the fact that language supports a particular feature, there must be a reason why the language designers thought of adding it.
Just a thought.
March 21, 2014 at 7:59 am
For the 'on topic' reply to this discussion: I've used a mix of the methods depending on the application / company / users. I've have the cursor based item by time approach, and the semi-global or global approach of building one or more roles and assigning the roles to users for that permission. Just as the answer to many questions in SQL Server is 'it depends', so does determining the 'best' approach for any given issue.
As for the 'slightly off topic' reply regarding cursors usage or the sometimes near violent reaction to their usage:
Like many things, there is a place and time. I try to avoid cursors generally, but there are times when they are the best solution.
For years I used to hear from senior DBAs "Don't ever use a cross join, they just cause issues.". However, one senior DBA sat me
down and told me that there are times when even the dreaded cross join was the best answer. When he fully explained the usage
and the 'what is happening under the covers' details, I found there there were (at that time and place) at least two and maybe three
solutions that could be simplified by using a cross join. Now I still tell junior people to avoid certain things 'until they know the proper usage'.
The long and short of this rambling is that anything and everything has its place and time in the sun.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply