karthik.catchme (11/30/2010)

---

Thanks for the Script. It is very useful. While scripting out the object level permissions, i think, the below line that has been highlighted (Underlined) should have been "QUOTENAME(SCHEMA_NAME(obj.schema_id))". Please correct me if i m wrong

/*********************************************/

/********* OBJECT LEVEL PERMISSIONS *********/

/*********************************************/

SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],

5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECTCASE

WHEN perm.state <> 'W' THEN perm.state_desc

ELSE 'GRANT'

END

+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) --select, execute, etc on specific objects

+ CASE

WHEN cl.column_id IS NULL THEN SPACE(0)

ELSE '(' + QUOTENAME(cl.name) + ')'

END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default

+ CASE

WHEN perm.state <> 'W' THEN SPACE(0)

ELSE SPACE(1) + 'WITH GRANT OPTION'

END

AS [-- SQL STATEMENTS --],

5 AS [-- RESULT ORDER HOLDER --]

FROM

sys.database_permissions AS perm