set affinity for SqlExpress: user

Showing posts with label user. Show all posts

Wednesday, March 28, 2012

How to show two dataset with equal & non equal of multiple selection.

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.

Please advice. Thanks.

warm regards
Rakin
Singapore.

It sounds like you are using RS 2005, since you have multi value report parameters.

On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:

Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value

The IN-filter can automatically deal with multi-value parameters (which represent an array of values).

-- Robert

|||

Thanks Robert.

You are right. I am using RS 2005.

Now, I am able to get the value for the first option. ( with IN )

But I could n't find any NOT IN option in the filter for the second set of results ?

Could you please advice for this ?

Regards
Rakin

|||

Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:

E.g. select * from products where ReorderLevel not IN (@.Level)

I also attached a small sample report at the bottom based on the Northwind database.

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.

The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.

Have any of you encountered anything like this?

Niall

How to show two dataset with equal & non equal of multiple selection.

Dear Friends,

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

Please advice. Thanks.

warm regards
Rakin
Singapore.

It sounds like you are using RS 2005, since you have multi value report parameters.

Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value

The IN-filter can automatically deal with multi-value parameters (which represent an array of values).

-- Robert

|||

Thanks Robert.

You are right. I am using RS 2005.

Now, I am able to get the value for the first option. ( with IN )

But I could n't find any NOT IN option in the filter for the second set of results ?

Could you please advice for this ?

Regards
Rakin

|||

Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:

E.g. select * from products where ReorderLevel not IN (@.Level)

I also attached a small sample report at the bottom based on the Northwind database.

-- Robert

|||

In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.

Have any of you encountered anything like this?

Niall

sql

How to show only records that belong to the logged in user.

Hello.

I realize that this question has been asked before, but I still can't get it to work. Below are some links that might be of help:

http://forums.asp.net/p/1159666/1913519.aspx#1913519
http://forums.asp.net/p/1161930/1924264.aspx
http://forums.asp.net/p/1116601/1732359.aspx#1732359
http://forums.asp.net/t/1104718.aspx
http://forums.asp.net/p/1096290/1655706.aspx#1655706
http://forums.asp.net/p/1110162/1707952.aspx#1707952

Basically, I need aDropDownListto displayonly projectsfor which thelogged in user is assigned asleader. The [Projects] table contains an integer ProjectId, a string ProjectName, a uniqueidentifier ProjectLeader, and other fields. Can someone help me with the SQL query and code?

* Here is the definition of the SqlDataSource:

<asp:SqlDataSource ID="SqlDataSource5" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDB.MDFConnectionString %>"
 SelectCommand="SELECT [ProjectId], [ProjectName] FROM [Projects] WHERE ([ProjectLeader] = @.Leader)" OnSelecting="SqlDataSource5_Selecting">
 <SelectParameters>
 <asp:Parameter Name="Leader" Type="Object" />
 </SelectParameters>
 </asp:SqlDataSource>

 * Here is the definition of the SqlDataSource5_Selecting method:

  protected void SqlDataSource5_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
 {
 e.Command.Parameters("@.Leader").Value = loggedInUserId;
 }
 
where loggedInUserId is a global variable of type System.Guid. It has been evaluated in the Page_Load event to as: loggedInUserId = (System.Guid)Membership.GetUser().ProviderUserKey;
 
Now the first problem I encounter is that when I run the page, the compiler complains and says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'." 
The second problem is when I insert the line: SqlDataSource5.SelectParameters("Leader").DefaultValue = loggedInUserId;
in page_Load. The compiler again says, "error CS0118: 'System.Data.Common.DbCommand.Parameters' is a 'property' but is used like a 'method'."
 
I've spent a long time trying to figure it out, but could not solve it. I would appreciate it if someone can help me out. Thank you very much.

change it to

e.Command.Parameters["@.Leader"].Value = loggedInUserId;

Brackets not paranthesis

|||

Thank you very much for your reply. It now compiles with seemingly no problems, but the DropDownList is empty. Without the WHERE clause, it would display all projects available.

I'm probably wrong, but it could be a databind problem. Any other mistakes you could spot?

Thanks.

|||

not sure try changing the type on the parameter to the type of that param so like

<asp:Parameter Name="Leader" Type="Int32" />

Do you have access rights to the DB so that you can run the SQL Profiler?

Try that and see what query is running against the DB...

Or try debugging it to see what that param is maybe it's not getting set right and you're actually setting it to 0

|||

Thanks again, PureWeen, for trying to help. I debugged the method that determines the Id of the logged-in user, and it correctly gave me {9a6fdee6-fe40-4545-b18a-c05a278080b5}, a valid uniqueidentifier.

Now I tried looking for the SQL Profiler by searching for it in the C drive, but it seems I don't have it installed. I'm using Microsoft SQL Server Management Studio Express.

|||

I also tried to insert the statement SqlDataSource5.SelectParameters["Leader"].DefaultValue = loggedInUserId.ToString(); in the Page_Load event, but the compiler complained, "error CS0029: Cannot implicitly convert type 'System.Guid' to 'string'."

So, I changed the statement to SqlDataSource5.SelectParameters["Leader"].DefaultValue = loggedInUserId.ToString(); and the DropDownList was still empty. I wonder if that statement is needed here in Page_Load.

|||

well you don't really have to set it in Page_Load event that might be your problem... Did you debug the Selecting Event to make sure that loggedInUserID does indeed equal the GUID at that point?

Instead of setting a value why not use a accessor method like

private Guid LoggedInUserID{
get{
return (System.Guid)Membership.GetUser().ProviderUserKey;

}

If you just delete the parameter and don't filter the query does the dropdownlist populate?

Can you try filtering on a different field in the table to test if that works?

|||

if that doesn't work maybe post more of your code or all of it :)

|||

Sorry for the late reply; I didn't have access to the source codefor the whole day, and I still don't. I'll try to answer from memory.

Basically,I can get it to work with strings, but not Guids. So I created a newfield in the table and called it ProjectLeaderName (a string), whichwould store the UserName of the user whose Id is ProjectLeader (auniqueidentifier). It worked fine this way. But when I modify the type fromString toObject, and use ProjectLeader (the Guid) instead ofProjectLeaderName (the String), the DropDownList displays nothing. Iwonder if the type "Object" is not the correct choice.

PureWeen:
wellyou don't really have to set it in Page_Load event that might be yourproblem... Did you debug the Selecting Event to make sure thatloggedInUserID does indeed equal the GUID at that point?

Iadded this chunk of code in Page_Load while I was desperate, and as itdidn't change a thing, I commented it out. The only code is now in theSqlDataSource5_Selecting method. And yes, the global variableloggedInUserId did get the right value as the one in the aspnet_Userstable when I was in debug mode.

PureWeen:
Instead of setting a value why not use a accessor method like
private Guid LoggedInUserID{
get{
return (System.Guid)Membership.GetUser().ProviderUserKey;
}
}

That's a tough one. I think the original intent wasthat I wanted to use a variable (loggedInUserId) as part of the SQLquery, but I failed miserably. Regardless, Its value is calculated in asimple method (which is called in Page_Load) thatsets theglobal variable but does not return it. I'm not overly familiar withaccessors or properties, although it looks like a quick, yet viable change I could make.

PureWeen:
If you just delete the parameter and don't filter the query does the dropdownlist populate?

Yep. Removing the WHERE clause shows all projects ever created in the DropDownList.

PureWeen:
Can you try filtering on a different field in the table to test if that works?

Yes,it works fine on strings (ProjectName) and ints (ProjectId), but not onGuids (ProjectLeader). That's why I added the new string fieldrepresenting thename of the project leader with the stored Id(ProjectLeaderName), and filtering by that works fine. I just call theproperty User.Identity.Name to return the name of the logged-in user.

PureWeen:

if that doesn't work maybe post more of your code or all of it :)

Sure. I won't have access to the code until Monday, so I'll post more relevant parts of it then.

Once again, your help is always appreciated. Thanks a lot.

|||

Can you build your own SqlCommand and pass it a Guid that returns filtered rows?

If you can then in the selecting event just replace the sqlcommand with yours..

or
MAybe specify the type on the parameter in the selecting event

cmd.Parameters[0].DbType = System.Data.DbType.Guid;

or
Maybe just skip having the parameter specified on the SqlDataSource parameter list at all then add it yourself ...
cmd.Parameters.Add("Name", System.Data.SqlDbType.UniqueIdentifier)

the SQL Profiler is in the management studio under Tools->Sql Profiler but I'm not sure if express has it

|||

Those are some interesting ideas I might have to try out. Unfortunately, I just realized I'm not very familiar with the logic or syntax of the statements above, so I think what I have to do is do some more reading until I can effectively tackle this out. As of right now, I'm content that it's working, even though I'm only using strings. Once I'm done reading, I should be more capable of correctly implementing your suggestions. I already have a good feeling one of them may work. I'll probably be ready in a week or so, so I'll keep you informed.

Thank you very much. Smile

|||

PureWeen:
Can you build your own SqlCommand and pass it a Guid that returns filtered rows?
If you can then in the selecting event just replace the sqlcommand with yours..

I tried to pass the SqlCommand, but for some reason it's not reading it. It may be a timing issue where I need to pass it before the Page_Load event, but it wouldn't. The DropDownList was still empty.

I did some more reading and searching, and one of the sources claimed that all I needed to do was delete the Object type attribute, simply because Guids are... special.

So what I had to do is simply replace<asp:Parameter Name="LeaderId"Type="Object"/> with<asp:Parameter Name="LeaderId"/>, and it worked fine.

PureWeen:
the SQL Profiler is in the management studio under Tools->Sql Profiler but I'm not sure if express has it

Too bad I can't find it here in the Express Edition.

So as a quick recap, the solution was to remove Type="Object" from the parameter, and it worked great.

Thank you, PureWeen, for setting me on the right track all that time. I appreciate your selfless efforts all that time.

Cheers.

How to show new Columns when user clicks on a cell in a report ?

Hi,
I have a requirement in my project which specifies that when the user clicks
on the drill down(or a cell), the report should show new columns which
basically describes the details of the drilldown action. Is it possible to
show new columns on drill down ? Currenlty Iam using Jump to Report
navigation property to show the details in a new report. Basically I want
new columns(which describe the cell clicked, for eg if the cell clicked is
total of all payments, I want to show details of the payment when user
clicks on it ) when user clicks on the cell. If I use drill down then all
detail columns are blank befor drill down action. Can anyone help me in
doing this ?
--
Thanks,
RKOn Oct 16, 3:59 pm, "S V Ramakrishna"
<ramakrishna.seeth...@.translogicsys.com> wrote:
> Hi,
> I have a requirement in my project which specifies that when the user clicks
> on the drill down(or a cell), the report should show new columns which
> basically describes the details of the drilldown action. Is it possible to
> show new columns on drill down ? Currenlty Iam using Jump to Report
> navigation property to show the details in a new report. Basically I want
> new columns(which describe the cell clicked, for eg if the cell clicked is
> total of all payments, I want to show details of the payment when user
> clicks on it ) when user clicks on the cell. If I use drill down then all
> detail columns are blank befor drill down action. Can anyone help me in
> doing this ?
> --
> Thanks,
> RK
Hi!
whene you use navigation and jum to report ,you can see parameters in
front of this,you must difine parameter in the second report and in
first report in property -navigation-parameter ,you choose your
parameter and fill your parameter with one field of dataset .
Regards.

Monday, March 26, 2012

how to show and edit parameters in a windows form

Is there somebody who has used a windows form and a suitable control (Like
Datagrid) to extract parameters of an specific report and let user edit
them?
I 've done the exctracting the parameters ,,but I don't know how to extract
each parameter values(which are based on the different queries) and show it
to the user.
Thanks for your help.
ALISee the Report Wizard demo code in the AwReporterWin WinForm app that
accompanies my book source code.
http://www.manning-sandbox.com/thread.jspa?threadID=10393&tstart=45
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"ALI-R" <newbie@.microsoft.com> wrote in message
news:eqEm5q1yEHA.2624@.TK2MSFTNGP11.phx.gbl...
> Is there somebody who has used a windows form and a suitable control (Like
> Datagrid) to extract parameters of an specific report and let user edit
> them?
> I 've done the exctracting the parameters ,,but I don't know how to
extract
> each parameter values(which are based on the different queries) and show
it
> to the user.
> Thanks for your help.
> ALI
>|||Thank you very much indeed,,I was actually doing the same thing ,but it
helped a lot.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:%23rF5hT4yEHA.2788@.TK2MSFTNGP15.phx.gbl...
> See the Report Wizard demo code in the AwReporterWin WinForm app that
> accompanies my book source code.
> http://www.manning-sandbox.com/thread.jspa?threadID=10393&tstart=45
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "ALI-R" <newbie@.microsoft.com> wrote in message
> news:eqEm5q1yEHA.2624@.TK2MSFTNGP11.phx.gbl...
> > Is there somebody who has used a windows form and a suitable control
(Like
> > Datagrid) to extract parameters of an specific report and let user edit
> > them?
> >
> > I 've done the exctracting the parameters ,,but I don't know how to
> extract
> > each parameter values(which are based on the different queries) and show
> it
> > to the user.
> >
> > Thanks for your help.
> > ALI
> >
> >
>

Friday, March 23, 2012

How to setup permissions by using field value?

Hello, I need some help. I am modifying a database that will be accessed by people in different parts of the world. I am trying to set user roles to show only records in each Location. Here is an example:

User Groups
Boston Users
New York Users
London Users

Database: machines
Table: tblmachines
fldname fldoperatingsystem fldgeocenter
--- -------- -----
foo.mydomain.com Windows 2000 BOSTON
loo.mydomain.com Windows NT 4.0 NEW YORK
moo.mydomain.com REDHAT LINUX LONDON

How do I configure user roles so that only users from Boston see Boston records, and users from New York see New York records etc etc? I am trying to key off the fldgeocenter field Any suggestions would be MOST welcome.User Roles doesn't help here.
The database roles does not help here.
The best way to filter the records horizontally is to implement stored procedures where "fldgeocenter" would be an input parameter. Use the value of that param. to filter the records in the WHERE statement.

Originally posted by dperrott
Hello, I need some help. I am modifying a database that will be accessed by people in different parts of the world. I am trying to set user roles to show only records in each Location. Here is an example:

User Groups
Boston Users
New York Users
London Users

Database: machines
Table: tblmachines
fldname fldoperatingsystem fldgeocenter
--- -------- -----
foo.mydomain.com Windows 2000 BOSTON
loo.mydomain.com Windows NT 4.0 NEW YORK
moo.mydomain.com REDHAT LINUX LONDON

How do I configure user roles so that only users from Boston see Boston records, and users from New York see New York records etc etc? I am trying to key off the fldgeocenter field Any suggestions would be MOST welcome.

how to setup LOCKS in ATL OELDB Consumer?

in sql server, we can use TABLOCK to lock a table, but how can i do this in ATL, without using a SP

i worry if two user perform db.Update() at the same time and refer to the same row, there may cause a problem.

i fond this line in the OLEDB ref.

Locking can occur with any of the methods that interact with rows on the data source. These include IRowsetLocate::GetRowsAt, IRowsetLocate::GetRowsByBookmark, IRowsetUpdate::Update, and others. These operations take locks and can wait for locks to be released by other transactions.

does this mean the locks are prefomed automatically ?

and, in the deferred update mode, if two user insert the same row into the DB, say, both of them insert 2 rows, 1 is the same, and the others are different, what would happen? what will the server return?

Locks are managed on the server side by the database engine.

Different cursor types (static, keyset or dynamic) and isolation levels can affect the server locking behavior. On the client, you can control cursor types and isolation level through OLEDB.

So the answer to the question regarding inserting the same row depends on the cursor type and iso level etc. If the table has an index that does not allow duplicate key, the 2nd insert could even fail. Basically, server will ensure transactional integrity is maintained for the data.

|||

so i learned the isolation levels, but what is a cursor type in OLEDB? i only heard that in ADO, do they have the same mean? and how can i set the cursor type in OLEDB

|||Cursor definition is the same across all API's. For ADO and ODBC, you can request a particular cursor type directly. OLEDB does this a bit differently, you request the behaviors of the cursor such as updatability, scrollabilty etc through the corresponding OLEDB properties, and OLEDB will find the most suitable cursor for you under the cover.

How to set user who cant modify any StoredProc and data in Tables but can read data in Tab

Please give me advise ????

Big Smile

Go into Management Studio, connect to your database and go to Security-->Users from the treeview. Right click and select New User. Create the use based on the relevant login and then go to the Securables option where you can add tables and stored procedures and select which rights the user will have.

Wednesday, March 21, 2012

how to setup db replication with objects owned by user other than dbo?

Hi, I was hoping someone could tell me how I am to replicate a database
between servers. The many of the tables, stored procedures and views are
owned by a user (e.g. USER1). The version running is SQL 2000. Lets take for
instance snapshot replication from Server1 to Server2.
Now I know that many people are having problems with replicating a database
and ending up realising that the tables, stored procs and views are now
owned by dbo. I want the replicated database objects owned by USER1. What do
I have to do? Do I:
1) create USER1 on the Server2,
2) and then start snapshot replication from Server1 assigning "Publication
properties > Articles > Article Defaults > Destination Table Owner" = USER1
?
3) then move across USER1's permissions to the replicated objects (not sure
how to proceed with this, I think i have to use ).
or do I:
1) create USER1 on the Server2,
2) use DTS to create empty table structures on Server2
3) and then assign USER1 the permissions
I am somewhat confused because USER1's permissions are dependent on the
tables being defined, and the tables are dependent on USER1 having ownership
on them. I dont know what to set up first.
Simply stated, how do I simply "set up a replication from scratch for a
database with objects owned by a user other than dbo"
Could anyone direct me?
Thanks!
PeterGenerate SQL Script of login,
Execute it on new server (create login + permissions +
add_role logins... )
then DTS the objects of the server (what you want) to the
other server...
>--Original Message--
>Hi, I was hoping someone could tell me how I am to
replicate a database
>between servers. The many of the tables, stored
procedures and views are
>owned by a user (e.g. USER1). The version running is SQL
2000. Lets take for
>instance snapshot replication from Server1 to Server2.
>Now I know that many people are having problems with
replicating a database
>and ending up realising that the tables, stored procs and
views are now
>owned by dbo. I want the replicated database objects
owned by USER1. What do
>I have to do? Do I:
>1) create USER1 on the Server2,
>2) and then start snapshot replication from Server1
assigning "Publication
>properties > Articles > Article Defaults > Destination
Table Owner" = USER1
>?
>3) then move across USER1's permissions to the replicated
objects (not sure
>how to proceed with this, I think i have to use ).
>or do I:
>1) create USER1 on the Server2,
>2) use DTS to create empty table structures on Server2
>3) and then assign USER1 the permissions
>I am somewhat confused because USER1's permissions are
dependent on the
>tables being defined, and the tables are dependent on
USER1 having ownership
>on them. I dont know what to set up first.
>Simply stated, how do I simply "set up a replication from
scratch for a
>database with objects owned by a user other than dbo"
>Could anyone direct me?
>Thanks!
>Peter
>
>
>.
>

How to set user who cant modify any StoredProc and data in Tables but can read data in Tab

Please give me advise ????

Big Smile

Monday, March 19, 2012

How to set up permissions for a user, who need to work with SQL Server Agent

I am looking for a good idea or best practice for setting up a security plan which allows users to execute a specific job, owned by the SQL Server Agent Service Account.

I tried to use the msdb SQLAgentOperatorRole, but unfortunately I recognized, that every local job could be executed.

I would like to have only one job executed by the user and all others should be visible, but not executable.

What is the best practice for this request? Can Proxies and credentials be helpful and if yes, how do I have to use them?

In other words: what is the best approach for this request (end user should see and start specific jobs in the SQL Server Agent).

Thanks in advance

Norbert

Owner can execute job as well.|||

Thanks for the hint, but I do not want to have this user as an owner. The owner should be another account. This user (developer) should only have the capability to execute jobs we (DBA) provided to him. He should not see ALL jobs nor should he execute ALL jobs. I would like to set permissions in a way that he can start/stop/enable/disable ONE specific job.

Is there an easy way to realize it? Or maybe a more complicated way?

Regards

Norbert

How to set up a report when a specific user makes changes to a database

Hi,
We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
2003 R2 SP2 server.
I successfully created a mailbox for SQL to use and associated the SQL Agent
to use this e-mail when sending out alerts.
I am however not able to find a way for an e-mail to be sent if a specific
user makes a change to a database.
Can somebody please point me in the right direction.
Thank you,
WarrenCan you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
Something else? Triggers and possibly event notifications seems to be what you want to look into.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
> Hi,
> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
> when sending out alerts.
> I am however not able to find a way for an e-mail to be sent if a specific user makes a change to
> a database.
> Can somebody please point me in the right direction.
> Thank you,
> Warren
>|||Basically anything that would be considered a change to the database for
auditing purposes.
Can you lead me in the right direction on setting up triggers or event
notifications? I am new to SQL 2005.
Thank you,
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
> Can you explain exactly what you mean by "makes a change to the database"?
> ALTER TABLE? UPDATE? Something else? Triggers and possibly event
> notifications seems to be what you want to look into.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows
>> 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||> Basically anything that would be considered a change to the database for auditing purposes.
Wow. Are you willing to pay the performance penalty for this?
Your best bet is probably to use some of the products out there. They use a combination of
server-side Profiler traces along with reading the transaction log (something which we cannot do
ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
time of doing it yourself.
Here's one: http://www.lumigent.com/products/auditdb.html
Some of the other log reader tools vendors might also have such products, I've listed the ones I
know of on my links page: http://www.karaszi.com/SQLServer/links.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
> Basically anything that would be considered a change to the database for auditing purposes.
> Can you lead me in the right direction on setting up triggers or event notifications? I am new to
> SQL 2005.
> Thank you,
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this e-mail
>> when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>|||Maybe I should refrase that a little bit.
Basically I need to have some type of checks and balances setup where an
alert is sent when the dba does something to a database or makes changes.
In the configuration we are using the dba may connect to the SQL server once
a quarter if that.
Warren
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
> Wow. Are you willing to pay the performance penalty for this?
> Your best bet is probably to use some of the products out there. They use
> a combination of server-side Profiler traces along with reading the
> transaction log (something which we cannot do ourselves) so performance
> impact would be less than if doing it ourselves. Not to mention the dev
> time of doing it yourself.
> Here's one: http://www.lumigent.com/products/auditdb.html
> Some of the other log reader tools vendors might also have such products,
> I've listed the ones I know of on my links page:
> http://www.karaszi.com/SQLServer/links.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for
>> auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL
>> Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>|||Did you look at the product?
There's no pre-packaged solution for this. You need to determine exactly what type of changes you
want to be alerted for. Based on that decide whether you find a product that suit your need or if
you want to do it yourself. Depending on what type of changes you want to be alerted for, event
notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
> Maybe I should refrase that a little bit.
> Basically I need to have some type of checks and balances setup where an alert is sent when the
> dba does something to a database or makes changes.
> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
> Warren
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot do
>> ourselves) so performance impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones I
>> know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am new
>> to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE? UPDATE?
>> Something else? Triggers and possibly event notifications seems to be what you want to look
>> into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>> e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a specific user makes a change
>> to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>|||Okay,
Maybe I really just need the basic and I can't figure out how to make it
work. Books on line wasn't very helpful.
Is there a white paper on setting up user auditing for just logging on?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
> Did you look at the product?
> There's no pre-packaged solution for this. You need to determine exactly
> what type of changes you want to be alerted for. Based on that decide
> whether you find a product that suit your need or if you want to do it
> yourself. Depending on what type of changes you want to be alerted for,
> event notification can be an option. See Books Online, CREATE EVENT
> NOTIFICATION.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an
>> alert is sent when the dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server
>> once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They
>> use a combination of server-side Profiler traces along with reading the
>> transaction log (something which we cannot do ourselves) so performance
>> impact would be less than if doing it ourselves. Not to mention the dev
>> time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such
>> products, I've listed the ones I know of on my links page:
>> http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database
>> for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event
>> notifications? I am new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the
>> database"? ALTER TABLE? UPDATE? Something else? Triggers and possibly
>> event notifications seems to be what you want to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> We just recently upgraded from SQL 2000 to SQL 2005 running on a
>> Windows 2003 R2 SP2 server.
>> I successfully created a mailbox for SQL to use and associated the
>> SQL Agent to use this e-mail when sending out alerts.
>> I am however not able to find a way for an e-mail to be sent if a
>> specific user makes a change to a database.
>> Can somebody please point me in the right direction.
>> Thank you,
>> Warren
>>
>>
>>
>>
>|||> Is there a white paper on setting up user auditing for just logging on?
Only login events. That makes it a very special case, and you can do that with a few clicks. SSMS,
Right-click the server in Object Explorer, Properties, the security window.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
news:%23OOnP485GHA.512@.TK2MSFTNGP06.phx.gbl...
> Okay,
> Maybe I really just need the basic and I can't figure out how to make it work. Books on line
> wasn't very helpful.
> Is there a white paper on setting up user auditing for just logging on?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eoaft5w4GHA.2596@.TK2MSFTNGP06.phx.gbl...
>> Did you look at the product?
>> There's no pre-packaged solution for this. You need to determine exactly what type of changes you
>> want to be alerted for. Based on that decide whether you find a product that suit your need or if
>> you want to do it yourself. Depending on what type of changes you want to be alerted for, event
>> notification can be an option. See Books Online, CREATE EVENT NOTIFICATION.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:ONw1e1w4GHA.4352@.TK2MSFTNGP03.phx.gbl...
>> Maybe I should refrase that a little bit.
>> Basically I need to have some type of checks and balances setup where an alert is sent when the
>> dba does something to a database or makes changes.
>> In the configuration we are using the dba may connect to the SQL server once a quarter if that.
>> Warren
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OtG$wOt4GHA.3376@.TK2MSFTNGP05.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Wow. Are you willing to pay the performance penalty for this?
>> Your best bet is probably to use some of the products out there. They use a combination of
>> server-side Profiler traces along with reading the transaction log (something which we cannot
>> do ourselves) so performance impact would be less than if doing it ourselves. Not to mention
>> the dev time of doing it yourself.
>> Here's one: http://www.lumigent.com/products/auditdb.html
>> Some of the other log reader tools vendors might also have such products, I've listed the ones
>> I know of on my links page: http://www.karaszi.com/SQLServer/links.asp
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:Ox6dFSj4GHA.3736@.TK2MSFTNGP02.phx.gbl...
>> Basically anything that would be considered a change to the database for auditing purposes.
>> Can you lead me in the right direction on setting up triggers or event notifications? I am
>> new to SQL 2005.
>> Thank you,
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:ujWBChZ4GHA.1496@.TK2MSFTNGP05.phx.gbl...
>> Can you explain exactly what you mean by "makes a change to the database"? ALTER TABLE?
>> UPDATE? Something else? Triggers and possibly event notifications seems to be what you want
>> to look into.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Warren Hollinshead" <whollinshead@.insmed.com> wrote in message
>> news:O2KVocZ4GHA.508@.TK2MSFTNGP06.phx.gbl...
>>> Hi,
>>> We just recently upgraded from SQL 2000 to SQL 2005 running on a Windows 2003 R2 SP2 server.
>>> I successfully created a mailbox for SQL to use and associated the SQL Agent to use this
>>> e-mail when sending out alerts.
>>> I am however not able to find a way for an e-mail to be sent if a specific user makes a
>>> change to a database.
>>> Can somebody please point me in the right direction.
>>>
>>> Thank you,
>>> Warren
>>>
>>>
>>
>>
>>
>

How to set up a report when a specific user makes changes to a database

How to set this up?

I need to set up the security on the Reporting Services such that user can
view the reports ONLY through a ReportViewer control on a asp.net web page
ie not allow to navigate all other folders or areas in the Report Manager.
Reporting services is installed on a stand alone server (not belong to any
domain, no AD set up) running on Windows 2003 server. Currently I configured
the "Report Manager" and "ReportServer" web site to allow annonyous access.
However some users know how to bypass the web application and directly enter
the IP address/reports of the stand alone server to view all the reports.
The asp.net page has built-in security to filter out the reports listed on
the page based on the user login and password. This web application is also
hosted at the stand alone server and is connected to a remote Sql server.
The web application is using the sql security.I have a similar problem and cannot find a solution provided by Microsoft.
However, I decidee to solve it by a trick. I develop an ASP.net web
application and the application have some report printing function. Whenever
a report is printed, a random key will be inserted into a database table
like this:
Key User
KE#n-asdfjk-ad33klj-kasjd WBush
The key and user are also passed in the query string. When the report is
run, the key and user will be checked again in the stored procedure to
verify that this user can print this report.
I am not sure whether this is a good solution, but I think that's what we
can do before Microsoft can provide a better solution.
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
> I need to set up the security on the Reporting Services such that user can
> view the reports ONLY through a ReportViewer control on a asp.net web page
> ie not allow to navigate all other folders or areas in the Report Manager.
> Reporting services is installed on a stand alone server (not belong to any
> domain, no AD set up) running on Windows 2003 server. Currently I
configured
> the "Report Manager" and "ReportServer" web site to allow annonyous
access.
> However some users know how to bypass the web application and directly
enter
> the IP address/reports of the stand alone server to view all the reports.
> The asp.net page has built-in security to filter out the reports listed on
> the page based on the user login and password. This web application is
also
> hosted at the stand alone server and is connected to a remote Sql server.
> The web application is using the sql security.
>|||Hi Paul.
The only way I know of is to have your web site's app pool use an identity
that is the only identity that is allowed to hit the reports.
For example, you might set up a service account called "WebApp1SvcAcct".
give that account and administrators group privileges in reporting services,
but not the rest of your users. This might be only half the solution. It's
been a while since we've had to deal with this.
-Tim
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I need to set up the security on the Reporting Services such that user can
>view the reports ONLY through a ReportViewer control on a asp.net web page
>ie not allow to navigate all other folders or areas in the Report Manager.
>Reporting services is installed on a stand alone server (not belong to any
>domain, no AD set up) running on Windows 2003 server. Currently I
>configured the "Report Manager" and "ReportServer" web site to allow
>annonyous access. However some users know how to bypass the web application
>and directly enter the IP address/reports of the stand alone server to view
>all the reports. The asp.net page has built-in security to filter out the
>reports listed on the page based on the user login and password. This web
>application is also hosted at the stand alone server and is connected to a
>remote Sql server. The web application is using the sql security.
>

Monday, March 12, 2012

How to set the current connectionString user as a defualt value?

Hi,

I'm using username & password in my connectionString to connect my program (C#.net) with Sql Server 2005..

I put in defualt value in one feild: user_name()

it replace the user: dbo, not the user which I used in my connectionString.

so.. how to get this user?

I tried to put: currnet_user

but I get the same result, "dbo" not the user in my connectionString..

I tried SESSION_USER, it gave the same result :(|||

This is my connectionString:

connectionString="Provider=SQLOLEDB;Data Source=LEWEHSBF\LEWEENG;Persist Security Info=True;Password=HsBfL4w4;User ID=Lewe;Initial Catalog=MyDatabase"

|||

I think the resone is the user I which I used in myConnectionString, is Admin user, I tried with "dataread, datawrite" user and its worked..

Thank you myself

how to set the autofresh parameter through expression?

if the user chooses type=a
then the autofresh is true,eg.every 10secs.It should be something like this:
=iif(Parameters!type = "a", 10, 0)
This same pattern is used to write lots of expressions for conditional
formatting for instance in the FontWeight property for a textbox cell
you can place:
=iif(Value > 0, Bold,Normal)
This will bold all values greater than 0. Notice that you don't in
this case you can reference just value, but if you you wanted to base
it off another cell you would need to do something like this:
=iif(ReportItems!Textbox1.Value > 0, Bold,Normal)
Occasionally values such as colors have to be in quotes e.g. "Black".
Others such as FontWeight have constants associated with them.
Collections that can be accessed in this way are:
ReportItems
Globals
Parameters
Fields|||thanks a lot,
how to get all the collections that can be accessed?
i mean the specific parameter? e.g the auto refesh of the report?
can you show me some sites that are helpful for beginner?|||Except for ReportItems all of the collections are enumerated when you
go into the expression editor for a cell or property. For ReportItems
just click on the item to find out its name. If it has a nonspecific
name I recommend renaming it before you reference it.
I haven't been able to find any good sites. Books on reporting services
will give you a basic overview, but I haven't found any that go beyond
that. Most of what I have learned has been hacking it out trial and
error, patient googling for references to specific topics, and digging
around Microsoft's documentation, which is fairly spartan. This news
group is really one of the best sources of information, and
unfortunately I only found it a few days ago. I access it through
google groups(there are other ways, I recommend google). Google lets
you search just this news group for information. The group has been
around a while so queries for 'expression' or 'collection' turn up lots
of hits. Search the group first before posting a question.|||i also found the google group a few days ago, my mother language is not
english,so it is a little difficult for me to express my question
clearly,i really know that what i have asked is old,but patient
googling for referernces means costing much time,unfortunately i have
little time on that project.
i really apprecaite those who help me.thanks.

Friday, March 9, 2012

How to set rights in E.Manager Since it is a open book..

Hi,

For working with linked servers , Mr. Hills had replied like , one can
not see the login rights etc if the particular user is not in the
admin role in the Enter prise manager.

Since the E.Manager is a open book so that any one can access any
database , Do you explain me When the sql server E.manager ks the
user's login and password .

With thanks

RAGHUWhen you register a server in EM you specify an authentication mode.

If you choose Windows Authentication then the login information is taken
from your Windows domain login - you will have access only to the databases
to which your login has been granted permissions.

If you choose SQL Server Authentication then you can opt either to save the
login name and password in the registry or to prompt for a login name each
time you try to connect to the server in EM. Your level of access is
determined by the SQL Server login name supplied.

For maximum security use Windows Authentication or set the option to prompt
for a login name each time you connect. If you choose Windows Authentication
then also password protect your screen saver so that your PC is secure when
you are away.

Does that answer your question?

--
David Portas
----
Please reply only to the newsgroup
--

How to set permissions for objects quickly

After I create a user (ref to database), then I need to assign 'select' &
'exec' rights at permissions for all objects. But there are over 1000
objects. How can I set the permissions quickly? Can I do it at query
analyzer?
Alternatively, what is the best way to setup this if want to add / rename
database username? Thanks.
"Pleo" <rx8@.hotmail.com> ¦b¶l¥ó news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl ¤¤
¼¶¼g...
> I'm not familiar sql. At enterprise server (sql2000) > security > logins >
> (want to change name here).
> Anyway, I guess it can't be changed there. Thanks.
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
> > Can you explain what you mean by "change sql login username"? Are you
> referring to the login name
> > (in master) or the user name (in your database)? Anyhow, you cannot
change
> the name of a login or a
> > user, You will be able to rename a user in 2005, not sure about login,
> though.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "Pleo" <rx8@.hotmail.com> wrote in message
> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
> > > As title, thanks!
> > >
> > >
> >
>
>SELECT permissions on all user tables and views can be assigned by adding
users to the db_datareader fixed database role. There is no such role for
executing procs but you can assign such permissions by creating your own
role and using a script like the one below to grant permissions on all
existing stored procedures:
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Pleo" <rx8@.hotmail.com> wrote in message
news:un0Y5Rw0FHA.1564@.tk2msftngp13.phx.gbl...
> After I create a user (ref to database), then I need to assign 'select' &
> 'exec' rights at permissions for all objects. But there are over 1000
> objects. How can I set the permissions quickly? Can I do it at query
> analyzer?
> Alternatively, what is the best way to setup this if want to add / rename
> database username? Thanks.
> "Pleo" <rx8@.hotmail.com> ¦b¶l¥ó news:ON0lytv0FHA.404@.TK2MSFTNGP09.phx.gbl
> ¤¤
> ¼¶¼g...
>> I'm not familiar sql. At enterprise server (sql2000) > security > logins
>> >
>> (want to change name here).
>> Anyway, I guess it can't be changed there. Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> ?
>> news:%23R4Ixpv0FHA.3068@.TK2MSFTNGP10.phx.gbl ?...
>> > Can you explain what you mean by "change sql login username"? Are you
>> referring to the login name
>> > (in master) or the user name (in your database)? Anyhow, you cannot
> change
>> the name of a login or a
>> > user, You will be able to rename a user in 2005, not sure about login,
>> though.
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://www.solidqualitylearning.com/
>> > Blog: http://solidqualitylearning.com/blogs/tibor/
>> >
>> >
>> > "Pleo" <rx8@.hotmail.com> wrote in message
>> news:eVAlmnv0FHA.2428@.tk2msftngp13.phx.gbl...
>> > > As title, thanks!
>> > >
>> > >
>> >
>>
>

Wednesday, March 28, 2012

How to show two dataset with equal & non equal of multiple selection.

How to show two dataset with equal & non equal of multiple selection.

How to show only records that belong to the logged in user.

How to show new Columns when user clicks on a cell in a report ?

Monday, March 26, 2012

how to show and edit parameters in a windows form

Friday, March 23, 2012

How to setup permissions by using field value?

how to setup LOCKS in ATL OELDB Consumer?

How to set user who cant modify any StoredProc and data in Tables but can read data in Tab

Wednesday, March 21, 2012

how to setup db replication with objects owned by user other than dbo?

How to set user who cant modify any StoredProc and data in Tables but can read data in Tab

Monday, March 19, 2012

How to set up permissions for a user, who need to work with SQL Server Agent

How to set up a report when a specific user makes changes to a database

How to set up a report when a specific user makes changes to a database

How to set up a report when a specific user makes changes to a database

How to set this up?

Monday, March 12, 2012

How to set the current connectionString user as a defualt value?

how to set the autofresh parameter through expression?

Friday, March 9, 2012

How to set rights in E.Manager Since it is a open book..

How to set permissions for objects quickly

How to set permissions for objects quickly

set affinity for SqlExpress

Blog Archive

About Me