Friday, March 30, 2012

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
Prasad
Not all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>
|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>
|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Not...icationService]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

No comments:

Post a Comment