I thought this really deserved its own post. One of the awesome kind.
http://www.zuzafun.com/let-the-war-begin
(spoiler: its an ad for razors)
Granting execute permissions to all stored procedures in your database.
1: DECLARE @T TABLE(RowID INT Identity( 1,1 ), Permission varchar(200))
2:
3: INSERT @T(Permission)
4: SELECT CASE ISNULL(DATA_TYPE,'') WHEN 'TABLE' THEN ' grant select on ' ELSE 'grant exec on ' END + QUOTENAME(ROUTINE_SCHEMA) + '.' +
5: QUOTENAME(ROUTINE_NAME) + ' TO [username]'
6: FROM INFORMATION_SCHEMA.ROUTINES
7: WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0
8:
9: DECLARE @text varchar(2000), @index int, @count int
10: SELECT @index = 1, @count = max(RowID) FROM @T
11: WHILE @index <= @count
12: BEGIN
13: SELECT @text = Permission FROM @T WHERE RowID = @index
14: exec (@text)
15: SET @index = @index + 1
16: END
Not that I would always recommend such a deed, however, if you are deploying your stored procedures to an external server, and should a need arise to grant permissions, there is always this code.
Knock yourself out if you want to encapsulate the above code as a stored procedure, and go ahead and create a web-service that would remotely grant execute permissions to your procedures.
Happy coding/hacking.
I swear I did not think to think if such a thing existed until I saw it on Gizmodo. But woalaaaa....it does..and trust me, it is a boon. I have had my own fair share of problems trying to open clamshell packages and have had a few markings left on my skin each time I try to open them. But this is oh-my-god-awesome. I guess I am gonna get me one of these at the earliest.
View video below
Manufacturer's web-site is http://plasticsurgeonopener.com