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
2022-02-23
498 reads
