Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <cfquery name="loc.results" cachedwithin="#CreateTimespan(0,2,0,0)#" datasource="#get('dataSourceName')#" username="#get('dataSourceUserName')#" password="#get('dataSourcePassword')#" >
- SELECT * FROM (
- SELECT User_name(p.grantor_principal_id)
- AS
- grantor,
- User_name(p.grantee_principal_id)
- AS grantee,
- Db_name()
- AS table_catalog,
- Schema_name(o.schema_id)
- AS table_schema,
- o.name
- AS TABLE_NAME,
- c.name
- AS column_name,
- CONVERT(VARCHAR(10), CASE p.TYPE WHEN 'SL' THEN 'SELECT' WHEN 'UP' THEN
- 'UPDATE'
- WHEN 'RF' THEN 'REFERENCES' END)
- AS privilege_type,
- CONVERT(VARCHAR(3), CASE p.state WHEN 'G' THEN 'NO' WHEN 'W' THEN 'YES'
- END) AS
- is_grantable
- FROM sys.database_permissions p,
- sys.objects o,
- sys.COLUMNS c
- WHERE o.TYPE IN ( 'U', 'V' )
- AND o.object_id = c.object_id
- AND p.class = 1
- AND p.major_id = o.object_id
- AND ( p.minor_id = c.column_id
- OR ( p.minor_id = 0
- AND NOT EXISTS (SELECT *
- FROM sys.database_permissions m
- WHERE m.class = 1
- AND m.major_id = p.major_id
- AND m.minor_id = c.column_id
- AND m.TYPE = p.TYPE
- AND m.state <> p.state) ) )
- AND p.TYPE IN ( 'RF', 'SL', 'UP' )
- AND p.state IN ( 'G', 'W' )
- AND 0 != ( Permissions(o.object_id, c.name) & -- back compat
- CASE p.TYPE
- WHEN 'RF' THEN 4 -- REFERENCES basebit
- WHEN 'SL' THEN 1 -- SELECT basebit
- WHEN 'UP' THEN 2 -- UPDATE basebit
- END )
- ) column_privileges
- <cfif IsDefined("arguments.where")>
- WHERE #PreserveSingleQuotes(arguments.WHERE)#
- </cfif>
- </cfquery>
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement