Find several user defined objects inside your database

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:

  • sys.objects
    You will find information for just-about-every user-defined object created within a database.
  • sys.types
    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.

Leave a Reply

Your email address will not be published. Required fields are marked *