Null values: roles

Showing posts with label roles. Show all posts

Friday, March 30, 2012

Role Permissions

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
"Pra

" <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
> Pra

>|||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
>
> "Pra

" <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::[[url]http://schemas.microsoft.com/SQL/Notifications/EventNotificationService[
/url]]
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
"Pra

" <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...
>

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
PrasadNot 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...
>> 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
>|||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/Notifications/EventNotificationService]
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...
>> 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
>>
>

role is not working

Hi,

I have done 2 roles in my analysis services project. one can see all (Role1) and the other can see only a portion of the dimension data (Role2). i tested these in analysis services browser using the Change User > Roles. They're both working ok. I tried the Change User > Current User. Supposedly, since I assigned my domain name in Role 2, I should be able to see only a portion of the data. But then, I could still see all. I also tried calling my cube from excel. Still, I could see everything..

Have I missed to set anything for this to work? Before I tested, I had processed the whole project...

cherriesh

You are probably also a server administrator if you are creating roles. Security is not applied to server administrators (you could give yourself any rights anyway). Try testing the role with a different account.sql

Role for access just information schema.

Which db fixes roles can I use to give a user permission to see all table
information but not the data itself ?
The db_datareader can select on everything, that's not what I want ! I want
to give access just to table information also, give access to the list of
logins and database users.
All readonly mode without seeing data.
Can anybody help on this ?
Thanks in advance,I just did a test that took me about 3 minutes. I added a login named
"test". I then added him as user to a db. No perimssions to do anything and
no roles involved. Just a user. I logged in as him. He can run sp_help, see
logins, and see db users. Am I not understanding your question correctly?
"Tim Conner" <timconner@.hotmail.com> wrote in message
news:#99Z6kPaDHA.1832@.TK2MSFTNGP10.phx.gbl...
> Which db fixes roles can I use to give a user permission to see all table
> information but not the data itself ?
> The db_datareader can select on everything, that's not what I want ! I
want
> to give access just to table information also, give access to the list of
> logins and database users.
> All readonly mode without seeing data.
> Can anybody help on this ?
>
> Thanks in advance,
>sql

Role design issue

I have a strange roles problem.

Is there a way we can accomplish this:

Say, the fact table has 100 records. each record having a new account number (100 accounts in 100 records).

The problem is we have users who have to see only some accounts. For example, user 1 has to see only accounts 1-10, user 2 has to see only accounts 11-20, user 3 has to see only accounts 21-30. and so on...

I know we can create different roles and create perspectives for these roles, but the problem is the list that defines what they can see is a table of 1000 user entries, each row has its own conditions for each user type. (Actually, each condition is a combination of different dimensions. For example, user 1 has to see account 1-10 and products A-D)

Does anyone have any idea on how to do this?

Thanks in advance

Luckily the problem you describe is very common. So common it has it's own name, it is oftenly referred as "Dynamic Dimension Security".

There are quite a few articles about it. Here is one for you http://sqljunkies.com/WebLog/mosha/archive/2004/12/16/5605.aspx

Search for it, you will find quite a bit information.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks Edward, I looked at it and I think thats what am looking for. will come back for your help if I cant figure something out :-)

Tuesday, March 20, 2012

Revoking and Granting permissions.. SQL Security Bug\Issue ?

I am getting a very strange issue occurring when granting
and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked
the EXEC permission on a stored procedure and when
attempting to run the function in my VB app which
referenced this procedure I got the error
message 'EXECUTE permission denied on stored
procedure...' which makes perfect sense. But when going
back and granting the permission back to the role when I
run the same function in my VB app I get a number
of 'SELECT permission denied on object.' errors.
And these SELECT statements are in reference to what the
stored procedure is running against ? Is this an issue
with SQL security ?
Can anybody provide any insight ?Hi,
I feel that some body have denied select permission on your user. This will
over rule the grant access given to the role assigned to your user. This can
be overcomed by granding select permission to your user, so as the deny will
be removed.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?|||Permissions on objects referenced by your stored procedure are not checked
as long as the following are true:
1) the objects have the same owner (unbroken ownership chain)
2) you are not referencing objects using dynamic SQL in your proc.
3) if the objects are in different databases and you are running SQL 2000
SP3, cross-database chaining needs to be enabled in the databases involved
and the object owners need to map to the same login (per #1 above)
You can read more about ownership chains in the SQL 2000 Books Online
<adminsql.chm::/ad_security_4iyb.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?

Saturday, February 25, 2012

Returning role memberships FROM sql Server

A problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.

Well if you have a table of logged in users you just have to do an Inner Join

SELECT *
FROM  LoggedInUsers liu INNER JOIN Roles r ON liu.role_id = r.id

Friday, March 30, 2012

Role Permissions

Role Permissions

Role Permissions

Role Permissions

Role Permissions

role is not working

Role for access just information schema.

Role design issue

Tuesday, March 20, 2012

Revoking and Granting permissions.. SQL Security Bug\Issue ?

Saturday, February 25, 2012

Returning role memberships FROM sql Server

Null values

Blog Archive

About Me