Null values

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
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 of login specified in GRANT UNSAFE...

For registering UNSAFE assemblies, we have to sign the dll and register it in SQL 2005. Also, the permission needs to be granted to a login..

Example code;

CREATE ASYMMETRIC KEY MyKey FROM EXECUTABLE FILE = 'C:\Assemblies\Interop.SQLDMO.dll'

CREATE LOGIN MyLogin

FROM ASYMMETRIC KEY MyKey

GRANT UNSAFE ASSEMBLY To MyLogin

What is the purpose of creating this MyLogin and how it serves to access the unsafe assembly.

Can anyone throw more light on this.

Thanks

Baskar

In SQL Server it is not possible to grant permissions to a key, only users or groups, so in order to keep the security model simple a way is provided to associate a key with a login. The reason why this is useful is that it provides a way for an administrator to selectively pick which assemblies he or she trusts enough to permit UNSAFE or EXTERNAL_ACCESS operations. You can mark a database as trustworthy, but that is not recommended.

--Bonnie

Role of login specified in GRANT UNSAFE...

For registering UNSAFE assemblies, we have to sign the dll and register it in SQL 2005. Also, the permission needs to be granted to a login..

Example code;

CREATEASYMMETRICKEY MyKey FROM EXECUTABLE FILE='C:\Assemblies\Interop.SQLDMO.dll'

CREATELOGIN MyLogin

FROMASYMMETRICKEY MyKey

GRANT UNSAFE ASSEMBLYTo MyLogin

What is the purpose of creating this MyLogin and how it serves to access the unsafe assembly.

Can anyone throw more light on this.

Thanks

Baskar

--Bonnie

Role of Distributors in transactional replication with queued updating

I am trying to understand what the value of installing the distribution
database on its own server. Performance is going to be an issue for clients
accessing the subscribing databases and I want to minimize the impact of the
replication process as much as possible. And by performance, I am not
referring to the speed of the replication process; it is acceptable for
there to be delays in replicating the data. With that being said, what is
the value of installing the distribution database on its own server for the
central subscriber as well as the regular subscribers?
Thanks
Hello William,
I think this depend on the performance of publication server. If it is not
heavy load, putting distributor on the same box shall have the best
performance because log reader agent and queue read agent can access
distributor locally. This will make clients have least impact of the
replication process.
However, if publication server is under heavy load, it is best that you
setup a standalone distributor. It has standalone process ability and
network bandwidth.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "William F. Kinsley" <bacardi@.noemail.nospam>
| Subject: Role of Distributors in transactional replication with queued
updating
| Date: Wed, 8 Jun 2005 11:55:03 -0400
| Lines: 14
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#WnJwJEbFHA.2520@.TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: host-67-62-103-180.cavtel.net 67.62.103.180
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:14186
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| I am trying to understand what the value of installing the distribution
| database on its own server. Performance is going to be an issue for
clients
| accessing the subscribing databases and I want to minimize the impact of
the
| replication process as much as possible. And by performance, I am not
| referring to the speed of the replication process; it is acceptable for
| there to be delays in replicating the data. With that being said, what
is
| the value of installing the distribution database on its own server for
the
| central subscriber as well as the regular subscribers?
|
|
|
| Thanks
|
|
|
sql

role not restricting as expected

On my Analysis Server, I had a user set up in the server role. I then added her to a role on one of my cubes restricting her by attribute on a certain dimension. The restriction did not take place since she was part of the server role.

I then removed her from the server role, and she is still seeing everything. The restriction is not taking place! I have duplicated these same actions for a different user, and the restrictions take place properly. I can also browse using the role (instead of the user), and the restrictions take place as expected.

Is there somewhere else (besides the server role setting) where something can be set giving a user "all-seeing rights"?

Also, is there a way to display the rights any given user has?Is she machine administrator ? Or perhaps member of database role with administrative permissions over the database ?|||It doesn't look like she had admin permissions on the db. I can't tell if she's a machine admin, but I assume that she it not. If you're a machine admin does that really give you rights to all the data in the cube?|||Yes - by default being machine admin also makes you a server admin. It is possible to turn this default behavior off however.

Role Nesting

Hi,

I am developing the security in a sql database, and i am doing so in a hierarchical way. In the white paper Row and cell security it says that we must add the child role as a menber of the parent role, but when you are in the role section you can not add roles as menbers of another role, so what i did was give the parent role ownership over the child role, it seemed logical.

So i build a admin
|
boss
|
worker
|
subworker

Nested Role

Now after giving users to those roles i was good to go and try my hierarchy security, i used the view that is in the white paper cell and row security which the code is the following:

SELECT ID, Label
FROM dbo.tblUniqueLabel WITH (NOLOCK)
WHERE (ID IN
(SELECT dbo.tblUniqueLabel.ID
FROM dbo.tblUniqueLabelMarking WITH (NOLOCK)
WHERE (CategoryID = 1) AND (IS_MEMBER(MarkingRoleName) = 1)))

But when i runned this view dont matter which users in which role ist always giving me an output of every single line in the table, the problem seem that he is giving me out the IS_MEMBER(MarkingRoleName) = 1 always as true like the user was in every single role.

What am i doing wrong?

Thanks.

What is MarkingRoleName evaluated to for each user?

Also, ownership is not the same as nesting. To make a role a member of another role, you can use sp_addrolemember.

For example,

sp_addrolemember role1, role2

will make role2 a member of role1.

Thanks
Laurentiu

Friday, March 30, 2012

Role Permissions

Role Permissions

Role of login specified in GRANT UNSAFE...

Role of login specified in GRANT UNSAFE...

Role of Distributors in transactional replication with queued updating

role not restricting as expected

Role Nesting

Null values

Blog Archive

About Me