Sometimes we need to find all tables that match a name pattern, or all stored procedures/functions/views. Or even types. Unfortunately there is no single View that includes all of them together. We have to search through several system tables to identify these objects.
I have recently ran into a similar situation, trying to automate the permissions’ granting for a specific user. I wanted to grant specific permissions on Tables, Stored Procedures, Scalar Functions and last but not least Types. Of course I decided to grant these permissions through a relevant database role and not directly to the user. This is a way better approach for several reasons.
There are many different Object Catalog Views for the many different types of objects on a database schema. After some googling and digging, I was able to complete my task with the use of the following ones:
You will find information for just-about-every user-defined object created within a database.
What you won’t find in the sys.objects view are the user-defined types. As if these are not so “user-defined”…
There are several useful examples in the above links (Microsoft is quite keen in this area) to help you find the objects of interest, according to their type, name, create-date etc.
The following example helped me create the SQL statements for granting EXECUTE and REFERENCES permissions on all scalar functions (type=’FN’) with a specific name prefix ‘DV_’ (or ‘dv_’ or ‘Dv_’ or ‘dV_’)
-- FIND all scalar FUNCTIONS with DV_ prefix select 'GRANT EXECUTE, REFERENCES ON [' + name + '] TO [Your-defined-role]' from sys.objects where lower(name) like 'dv_%' and type='FN';
Notice the use of lower() function on name to avoid any case sensitivity issues in our query.
By changing ‘FN’ to ‘P’ you can get the relevant stored procedures instead.
-- FIND all PROCEDURES with DV_ prefix select 'GRANT EXECUTE ON [' + name + '] TO [Your-defined-role]' from sys.objects where lower(name) like 'dv_%' and type='P';
Finally types can be derived from the following query
-- FIND all TYPES with DV_ prefix select 'GRANT CONTROL, REFERENCES ON TYPE::[' + name + '] TO [Your-defined-role]' from sys.types where lower(name) like 'dv_%';
In the end don’t forget to add your user as member to the newly privileged role!
-- ADD members to the role ALTER ROLE [Your-defined-role] ADD MEMBER [Your-defined-user] GO
If you found this post useful please share it or comment on it.