December 2, 2011 at 1:46 pm
Hello,
Pardon my ignorance ...I can't seem to find a script to query this case?
i'm sure it's somewhere on sqlservercentral
Thx again
JR
December 2, 2011 at 1:53 pm
is this what you want?
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' ON ['
+ OBJECT_SCHEMA_NAME(major_id) + '].[' + OBJECT_NAME(major_id) + ']'+
case minor_id
when 0 then ' '
else
' (['+col_name(sys.database_permissions.major_Id, sys.database_permissions.minor_id) + '])'
end
+' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- object/column permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class not in (0,3) and major_id = ISNULL(OBJECT_ID(@as_ObjectName), major_id)
ORDER BY USER_NAME(grantee_principal_id),OBJECT_SCHEMA_NAME(major_id), OBJECT_NAME(major_id)
if not may get you started
---------------------------------------------------------------------
December 2, 2011 at 2:02 pm
thanks for that ! i'm on 2005
Although giving me an error
Msg 137, Level 15, State 2, Line 16
Must declare the scalar variable "@as_ObjectName".
December 2, 2011 at 3:34 pm
sorry, that was a section of a larger script, add
declare @as_ObjectName sysname
set @as_ObjectName = NULL
---------------------------------------------------------------------
December 5, 2011 at 6:31 am
Thank you very much ..works perfectly now !
I may need more help some other queries
December 5, 2011 at 11:56 am
I need more help in determining more server level permissions
1- list of users that have the 'ALTER SETTINGS' level permission
2- list of users that have the 'CONTROL SERVER' level permission
Any help with this query would be much appreciated
thx !
December 5, 2011 at 1:20 pm
I've actually tried that 2nd script but it doesn't return anyone with either server permission even when commenting the
WHERE server_principals.type IN ( 'S', 'U', 'G' )
, is that normal?
December 6, 2011 at 1:52 am
sorry, I've tried the script and it returns those two permissions for me. Have those rights been granted to anyone in your setup?
---------------------------------------------------------------------
December 6, 2011 at 6:27 am
That's strange.... thanks anyways for the tips, much appreciated.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply