Grant execute to all table types at once

Question

Grant execute to all table types at once

tommiwan

Mr or Mrs. 500

Points: 593
More actions
January 31, 2024 at 6:13 pm

#4370914

In our database we have a lot of table types. I'm looking for a way to grant EXECUTE on all tables types at once. I'd also be interested in granting it to all table types in one particular schema. So far, everything I can find gives the syntax for granting execute to one table type at a time. I don't want to grant execute at the entire schema level because that would give access to stored procedures. The best I've come up with so far is a sql query that selects the grant statement plus the schema and name from sys.table_types. I don't love granting object level permissions. Is there a cleaner way to do this? This is on an Azure SQL database, but would be applicable to all versions of SQL.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Create a role and grant the execute to the table types once to this role, then when new people need that access grant them access to the role.

CREATE ROLE TableTypeExecute
GO
GRANT EXEC ON MyTableType TO TableTypeExecute
GO
Rinse and repeat

Then when people need access its just a simple 1 line

ALTER ROLE TableTypeExecute ADD MEMBER MyUser

Or even better use AD groups / Entra ID Groups and then it's up to the user provisioning / AD team / service desk to grant access to the individuals to the group and then they inherit the permissions.

tommiwan Mr or Mrs. 500 Points: 593 More actions · Answer 2

Thanks for the idea. A role to help organize these permissions would definitely help.

We have 87 table types in our DB and I was looking for a way to grant execute to all 87 at once. The problem is if I use GRANT EXECUTE ON SCHEMA then I am giving execute to stored procedures as well and that is too much. My current solution is to generate grant EXECUTE statements on each table type using some dynamic sql and then execute those grant statements. Here's the code. If anyone has a better way let me know.

SELECT 
'GRANT EXECUTE ON TYPE::' + S.Name + '.' + TT.Name + ' TO <User>',
* 
FROM sys.table_types TT
INNER JOIN sys.schemas S
ON TT.schema_id = S.schema_id
WHERE S.Name = <SchemaName>

Ant-Green SSC Guru Points: 113445 More actions · Answer 3

Yeah that would be the way to do it, an initial dynamic one off grant to a role.