Regarding Accessing on every objects within the sql 2005

Hi guys , can I know is that any way to set up some authentication for user access all the objects within the database after login successfully. For example, after access login into the database server and would like to click on certain database (eg : A) , then pop up the user access login page again. Thxcould you explain this a bit more, I did′n′t get your point from the explanation.

Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Sorry for the short explanation. Er... let me give a scenario. Once I open SQL management studio, there's a user login pop up window in order I able to access into my server. After login successfully,there are 3 database displays and stored in my servers ( Db A,B and C). When I want to click on the A database, then will pop up the user login window again for authorization. After that, if I want to choose a specific object (eg: Table 'Test') , then there's the use login window pop up again for authorization. At here, which means that authorization will show first before proceed any actions on any objects from the database in my server,.

So is there any possible to make such scenario? I'm just curious about it and would like to enhance my SQL security for my server and away from data thefting (For example, while away from the pc for a while and there's a staff come towards the pc for viewing and modifying the data). Thx for any assistance. Have a nice day.

From,

Hans

|||

Hi,

no SSMS does not have this functionality. once autenticated, permissions are checked upon access to the objects not prompting for any credentials again.

HTH, jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You should lock your console when away from your machine.

Thanks
Laurentiu

|||Thx for the advise. Seems like SQL 2005 doesn't have ' Always prompt for username and password login ' feature which in SQL 2000 then.|||

There was no such feature in SQL Server 2000. Maybe you are referring to a client feature, in which case you should post your inquiry on the Tools forum.

Thanks
Laurentiu

Reg. Log Shipping.

I have configured a log shipping on one of my Database.
Both the two jobs i.e. copy log and resotre log jobs run successfully
on the destination machines without any problem but I dont see any
logs copied on the destination machine.
1.Initially I had a job which used to take the transaction log
backup ,I thought this might be the reason as all the transaction are
getting backed up log shipping copy job is not getting any transaction
log to copy to the destination server.
2.But after disabling the transaction log backup job also samething is
happening i.e. log shipping copy and restore job is running
successfully but i dont see anything copied or resotred.
I must be doing something wrong but dont know whats that..
Please help.
Thanks
SudhirHi
Are you sure that job was running? Have you looked at view job history?
"Sudhir" <sam904@.gmail.com> wrote in message
news:1182320095.674384.196630@.u2g2000hsc.googlegroups.com...
>I have configured a log shipping on one of my Database.
> Both the two jobs i.e. copy log and resotre log jobs run successfully
> on the destination machines without any problem but I dont see any
> logs copied on the destination machine.
> 1.Initially I had a job which used to take the transaction log
> backup ,I thought this might be the reason as all the transaction are
> getting backed up log shipping copy job is not getting any transaction
> log to copy to the destination server.
> 2.But after disabling the transaction log backup job also samething is
> happening i.e. log shipping copy and restore job is running
> successfully but i dont see anything copied or resotred.
> I must be doing something wrong but dont know whats that..
> Please help.
> Thanks
> Sudhir
>

Reference Package Level Variables in a Script Component.

I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.

Any help will be appreciated.

Thanks,

Andy.

Type "Me." and let intellisense help you from there.

-Jamie

|||

Jamie,

Thanks for your suggestion. I did that and I was able to locate the variable. What does "ME" refer to? Is it refering to the Script Component? Does typing ME in different procedures/functions within the Script Component refer to different things?

Thanks,

Andy.

|||

Now that I can reference the variable I am getting script component errors.

I am basically using the RowCount Component variable and reading into the script and writing it out to the destination.

I put the RowCount Variable in the ReadWriteVariables property of the Script Component since having it in the ReadOnlyVariables was hanging the application. But when I put it under the ReadWriteVariables, I get the following error.

The collection of variables locked for read and write access is not available outside of PostExecute.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.Variables.get_CountID()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.WriteTrailerCount2()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

|||

OK, a couple of things:

1) Are your script and rowcount components in the same data-flow? This is not a good idea because the Rowcount component will not populate the variable until the data-flow is complete. If you want to use that value in the script component then you will have to break the data-flow into 2 and pass the data between the 2 using a raw file.

2) You can't reference variables listed in ReadWriteVariables inside the Input0_ProcessInputRow(Row) method. You can override the PostExecute() method which gets called when all the rows in the pipeline have passed through the component. ReadWriteVariables CAN be accessed in there.

What is it you're trying to achieve here?

-Jamie

|||

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer? I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

3) Is there any documentation on the sequence of execution of methods in the script component?

|||

Andy_1979 wrote:

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

No problem. We're all here to learn right? :)

Andy_1979 wrote:

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

Well this may not actually physically be the case but logically that is how you should think of it. There is certainly no guarantee that a variable populated in a component will be readable by another component in the same data-flow. You should never attempt to do this.

Don't think of the components as seperate "things". The lowest unit of execution is a data-flow. The components are just the constituent parts of that and it helps to think of them as of they are actually all executing at the same time

Andy_1979 wrote:

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer?

Well it depends what you want to do. If you want to write it to the output buffer then you should be able to do this by splitting the data-flow into 2 as I previously suggested.

I'm actually more concerned about why you want to do this. I know I don't know your requirement but do you really need to put the same value into every single row in the pipeline? That strikes me as a strange thing to do.

Andy_1979 wrote:

I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

The reason putting it in ReadOnlyVariables causes it to hang is because you are trying to read from it before the Rowcount component has finished with it. The Rowcount component has locked the variable. I refer you to my earlier point about how a data-flow executes. it is not as simple as just a series of sequential operations even though it looks as though it is.

Andy_1979 wrote:

3) Is there any documentation on the sequence of execution of methods in the script component?

Errr... probably yeah. Its fairly intuitive though. Perhaps this article in BOL will help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2a0aae82-39cc-4423-b09a-72d2f61033bd.htm

Why do you want to put this value into the pipeline?

-Jamie

|||

Jamie,

Thank you so much for taking time to explain all of this. The RowCount Variable is written out to destination at the end of processing as a new row. It is not outputted on every single row. I basically want to count the number of input rows coming into my script component, process them and write them out but at the end write out an additional line which has the count of total rows processed.

Andy.

|||

Well it sounds as though the extra row you want to output will most likely have different column metadata to the rest of the pipeline and if that's the case then you won't be able to do this.

But...if you do want to...then here's what you should do.

Use a script component|||This whole variable story is a joke isn't it?|||

orbit wrote:

This whole variable story is a joke isn't it?

That's not a particularly useful statement. What do you mean? Do you have feedback for the product? If so, submit it at Microsoft Conenct.

-Jamie

Reference Package Level Variables in a Script Component.

I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.

Any help will be appreciated.

Thanks,

Andy.

Type "Me." and let intellisense help you from there.

-Jamie

|||

Jamie,

Thanks for your suggestion. I did that and I was able to locate the variable. What does "ME" refer to? Is it refering to the Script Component? Does typing ME in different procedures/functions within the Script Component refer to different things?

Thanks,

Andy.

|||

Now that I can reference the variable I am getting script component errors.

I am basically using the RowCount Component variable and reading into the script and writing it out to the destination.

I put the RowCount Variable in the ReadWriteVariables property of the Script Component since having it in the ReadOnlyVariables was hanging the application. But when I put it under the ReadWriteVariables, I get the following error.

The collection of variables locked for read and write access is not available outside of PostExecute.

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.Variables.get_CountID()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.WriteTrailerCount2()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

|||

OK, a couple of things:

1) Are your script and rowcount components in the same data-flow? This is not a good idea because the Rowcount component will not populate the variable until the data-flow is complete. If you want to use that value in the script component then you will have to break the data-flow into 2 and pass the data between the 2 using a raw file.

2) You can't reference variables listed in ReadWriteVariables inside the Input0_ProcessInputRow(Row) method. You can override the PostExecute() method which gets called when all the rows in the pipeline have passed through the component. ReadWriteVariables CAN be accessed in there.

What is it you're trying to achieve here?

-Jamie

|||

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer? I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

3) Is there any documentation on the sequence of execution of methods in the script component?

|||

Andy_1979 wrote:

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

No problem. We're all here to learn right? :)

Andy_1979 wrote:

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

Well this may not actually physically be the case but logically that is how you should think of it. There is certainly no guarantee that a variable populated in a component will be readable by another component in the same data-flow. You should never attempt to do this.

Don't think of the components as seperate "things". The lowest unit of execution is a data-flow. The components are just the constituent parts of that and it helps to think of them as of they are actually all executing at the same time

Andy_1979 wrote:

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer?

Well it depends what you want to do. If you want to write it to the output buffer then you should be able to do this by splitting the data-flow into 2 as I previously suggested.

I'm actually more concerned about why you want to do this. I know I don't know your requirement but do you really need to put the same value into every single row in the pipeline? That strikes me as a strange thing to do.

Andy_1979 wrote:

I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

The reason putting it in ReadOnlyVariables causes it to hang is because you are trying to read from it before the Rowcount component has finished with it. The Rowcount component has locked the variable. I refer you to my earlier point about how a data-flow executes. it is not as simple as just a series of sequential operations even though it looks as though it is.

Andy_1979 wrote:

3) Is there any documentation on the sequence of execution of methods in the script component?

Errr... probably yeah. Its fairly intuitive though. Perhaps this article in BOL will help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2a0aae82-39cc-4423-b09a-72d2f61033bd.htm

Why do you want to put this value into the pipeline?

-Jamie

|||

Jamie,

Thank you so much for taking time to explain all of this. The RowCount Variable is written out to destination at the end of processing as a new row. It is not outputted on every single row. I basically want to count the number of input rows coming into my script component, process them and write them out but at the end write out an additional line which has the count of total rows processed.

Andy.

|||

Well it sounds as though the extra row you want to output will most likely have different column metadata to the rest of the pipeline and if that's the case then you won't be able to do this.

But...if you do want to...then here's what you should do.

Use a script component|||This whole variable story is a joke isn't it?|||

orbit wrote:

This whole variable story is a joke isn't it?

That's not a particularly useful statement. What do you mean? Do you have feedback for the product? If so, submit it at Microsoft Conenct.

-Jamie