Technical Article

Grant EXECUTE on all user-defines types

,

After giving datawriter access on a table for a user, i realized i also needed to give EXECUTE permission on the user types used in the table.

Since i didnt mind to give access to user-defined types, this script gives execute permission on all user-defined types for the specific user.

DECLARE @usersysname = 'your_user'
DECLARE @namesysname
DECLARE @cmdNVARCHAR(4000)

DECLARE usertypes CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.types WHERE is_user_defined = 1
OPEN usertypes
FETCH usertypes INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'GRANT EXECUTE ON TYPE::' + @name + ' TO [' + @user + ']'
EXEC (@cmd)
FETCH usertypes INTO @name
END
CLOSE usertypes
DEALLOCATE usertypes

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating