This will list explicitly granted permissions on table types, but not those granted implicitly through role or group membership, or permissions granted against the containing schema.
SELECT
[schema] = s.name,
[type] = t.name,
[user] = u.name,
p.permission_name,
p.state_desc
FROM sys.database_permissions AS p
INNER JOIN sys.database_principals AS u
ON p.grantee_principal_id = u.principal_id
INNER JOIN sys.types AS t
ON p.major_id = t.user_type_id--.[object_id]
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE p.class = 6; -- TYPE
I am curious what type of explicit permissions you are using in your system for table types? From the documentation there isn't a whole lot you need to implement for standard runtime query support (you can't grant SELECT
directly, for example). It seems this is mostly there for metadata / control.
This isn't as easy to achieve as you might think. One way is to create a new user-defined database role, give all the permissions needed to that role, then add users to the new role. This at least makes it easier to give users (or other user-defined roles) this set of permissions in future. The following steps are a good start:
-- The user-defined role containing all required permissions
CREATE ROLE Readers AUTHORIZATION dbo;
-- Give read-only access to all tables,
-- views on those tables, and in-line
-- functions
ALTER ROLE db_datareader ADD MEMBER Readers;
-- Example: add a user (Bob) to the role
ALTER ROLE Readers ADD MEMBER Bob;
After this, Bob will have wide read-only privileges in the database. He will be able to read from all tables, views on those tables, and in-line functions. He will not be able to execute any procedures or use non-inline functions, however.
You will need to grant specific permissions to the Readers role for safe functions and procedures you wish Readers to have access to. There may be steps you can take to make this process easier (such as grouping objects into a schema and granting execute permission on the schema instead of individual objects) but there are too many details to cover here.
One thing to be aware of is that the lack of data modification privileges would not prevent a Reader from changing data via a stored procedure she has been granted execute permission on, if the procedure and object being modified share a common owner. Nor would an explicit deny be respected in this case. This feature is known as Ownership Chaining.
Best Answer
This should do the work. I tested it in my test environment, works for me.