Null values: objects

Showing posts with label objects. 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
>>
>

Wednesday, March 28, 2012

RMO Equivalent for Merge Replication T-SQL Procedures

We want to have some information about RMO (Replication Management Objects in SQL Server 2005) equivalent for the following Merge Replication T-SQL procedures.

sp_changemergearticle
sp_helpmergearticleconflicts
sp_helpmergeconflictrows
sp_helpmergedeleteconflictrows
sp_deletemergeconflictrow

Since I was not aware of these RMO properties, here is a response from someone who has that knowledge.

Changing properties in MergeArticle object should be calling sp_changemergearticle.

Replicationdatabase.EnumCOnflcitTables should be equivalent to sp_hlepmergecarticleConflicts.

I don’t think we have any RMO methods that are equivalent to the last 3 proc calls

|||Thanks Mahesh, for your information. We will check that, if you can find any equivalent implementation for other three methods or any work around in order to use the other three methods that would be great ful.

Thanks
VDeevi.|||Sure, I will let you know.
Meanwhile, cant you wrap the other calls as a sql statement and make the calls?Also, you could add your logic in that! wouldnt that work for you?|||Thanks for your information. We will try to build the components based on the sproc calls to these stored procedures using ADO.Net. Once again thanks for your help.

Thankssql

Saturday, February 25, 2012

Returning objects from CLR Stored Procedure

Hello All

I have a few questions about how to return a DataSet object from a CLR Stored Procedure as an output parameter:

Can this be done? I am having problems at execution time with declaring an output parameter of the type "DataSet". "Cannot find type DataSet" message, compiler/build does not complain, error occurs at execution time.

This seems like one of the most obvious ways to create a more object-oriented approach using the technology offered in the 2005 product stack
(.NET2.0/VS2005/SQL2005),
Do you agree? if not, what other design would you use.

If this cannot be done, returning the serialization of the DataSet would be my next preference. There appears to be extra steps required to pregenerate serializer assemblies with sgen, any good detailed references how to do this?

I hope my questions are clear and I will provide code examples or clarification on request.

Look forward to feedback, Thanks in advance Smile

A .NET proc has to have datatypes compatible with T-SQL types, as the .NET code will be wrapped in a T-SQL proc. So returning a DataSet as output param is out of the question.

Is there any particular reason you need to return a DataSet? Can't you just return a datareader through the "normal" SqlPipe.Send(DataReader)?

As for returned a serialized DataSet, the same issues as with the dataset as an output param still stands; if you want to use an output param - the type has to be SQL compatible.

As for Xml serialization in general, you need to pregen the serialization assembly by using sgen (exactly as you say). So what you do is, you run sgen over your assembly/class which you serialize and catalogue the sgened assembly oin SQL Server.

Niels
|||

Thank you for your feedback Niels

//So returning a DataSet as output param is out of the question.
I thought that was the case, just wanted confirmation

//Is there any particular reason you need to return a DataSet?
Basically, to create an Object Oriented Data Access Layer in an n-tier architecture.
Stored procedures capable of returning objects, in my opinion, would be a significant progammability enhancement
The DataSet object containing multiple result sets seemed like a good mechanism

//Can't you just return a datareader through the "normal" SqlPipe.Send
//(DataReader)?
Isn't there an 8K limit on the pipe? My objects can potentially be larger

//As for returned a serialized DataSet, the same issues as with the dataset as
//an output param

Can I put the XML in a SQLCHARS or SQLBYTES variable and return it in an output parameter?

//run sgen over your assembly/class which you serialize and catalogue the sgened assembly
I thought this was what I did, will try again

Thanks again
Scott

|||

//Isn't there an 8K limit on the pipe? My objects can potentially be larger

The pipe can send whatever size. However, you can not send objects. So what you'd send is either the raw data or XML. In either case you'd need to do some processing client side, if you want OO.

////As for returned a serialized DataSet, the same issues as with the dataset as
////an output param

//Can I put the XML in a SQLCHARS or SQLBYTES variable and return it in an output //parameter?

Sorry, I had a brain-fart when I wrote the above about serialized data set. If you serialize it into XML you can just return the xml as an output param.

Niels

|||Scott,

Take a look at this link:
http://www.codeproject.com/cs/database/PassingArraysIntoSPs.asp

They take DataTables and Arrays and convert them to binary data and pass them in as IMAGE types. I'm sure you can extend this idea to have that IMAGE type passed back to .NET code where you can convert it back into DataTable format.

I know this is working with DataTables instead of DataSets but perhaps it is enough to allow you to do what you need to do.

As a worst-case scenario you could always have pass in some connection information and IDs and have your SP communicate with a Web Service to get the real data passed in and out.

-Shane

Friday, March 30, 2012

Role Permissions

Role Permissions

Role Permissions

Role Permissions

Role Permissions

Wednesday, March 28, 2012

RMO Equivalent for Merge Replication T-SQL Procedures

Saturday, February 25, 2012

Returning objects from CLR Stored Procedure

Null values

Blog Archive

About Me