Deployment issue - users cannot view reports

Hi All,
I am having some difficulty allowing users to view reports. I have
developed a series of reports on RS and deployed them to Report Manager.
However It is only myself that can view the reports on report mnanager -
whenever anyone else tries to log on and view them they get a blank page -
what am i doing wrong?
Thanks and HAPPY XMAS!!
GregGreg wrote:
> Hi All,
> I am having some difficulty allowing users to view reports. I have
> developed a series of reports on RS and deployed them to Report
> Manager. However It is only myself that can view the reports on
> report mnanager - whenever anyone else tries to log on and view them
> they get a blank page - what am i doing wrong?
>
> Thanks and HAPPY XMAS!!
> Greg
It may sound like a stupid question but ... have you checked their
permissions!
If they can see the Report Manager, but not see any reports, then the
security on the reports is too tight.
As administrator you will be able to click on properties and security
of the various folders and reports and add the appropriate users or
groups.
After that you will need to make sure the Datasources have suitable
logon credentials set too.
Regards (and merry christmas!)
Chris|||Hi chris - thanks for the pointer - ill check it out and get back!
"Chris McGuigan" wrote:
> Greg wrote:
> > Hi All,
> >
> > I am having some difficulty allowing users to view reports. I have
> > developed a series of reports on RS and deployed them to Report
> > Manager. However It is only myself that can view the reports on
> > report mnanager - whenever anyone else tries to log on and view them
> > they get a blank page - what am i doing wrong?
> >
> >
> > Thanks and HAPPY XMAS!!
> >
> > Greg
> It may sound like a stupid question but ... have you checked their
> permissions!
> If they can see the Report Manager, but not see any reports, then the
> security on the reports is too tight.
> As administrator you will be able to click on properties and security
> of the various folders and reports and add the appropriate users or
> groups.
> After that you will need to make sure the Datasources have suitable
> logon credentials set too.
> Regards (and merry christmas!)
> Chris
>|||I have what may be a similar issue. All users on our local intreanet can
view the reports, but people from the outside cannot. They can browse to
the page, and see the list of reports, but when they try to view a report,
they get "The Page cannot be Displayed".
Could this be the same as Greg's problem?
Thanks,
Daniel Williams
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:48094DE4-52C0-4522-A822-352BADD676E4@.microsoft.com...
> Hi All,
> I am having some difficulty allowing users to view reports. I have
> developed a series of reports on RS and deployed them to Report Manager.
> However It is only myself that can view the reports on report mnanager -
> whenever anyone else tries to log on and view them they get a blank page -
> what am i doing wrong?
>
> Thanks and HAPPY XMAS!!
> Greg
>|||This is different. In the current version, Report Manager uses a frame to
access the reports. You probably need to set the ReportServerExternalURL
property. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/drp_deploying_v1_0h9e.asp.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Daniel Williams" <daniel@.aniworld.com> wrote in message
news:eabjGuP7EHA.2568@.TK2MSFTNGP10.phx.gbl...
> I have what may be a similar issue. All users on our local intreanet can
> view the reports, but people from the outside cannot. They can browse to
> the page, and see the list of reports, but when they try to view a report,
> they get "The Page cannot be Displayed".
> Could this be the same as Greg's problem?
> Thanks,
> Daniel Williams
> "Greg" <Greg@.discussions.microsoft.com> wrote in message
> news:48094DE4-52C0-4522-A822-352BADD676E4@.microsoft.com...
>> Hi All,
>> I am having some difficulty allowing users to view reports. I have
>> developed a series of reports on RS and deployed them to Report Manager.
>> However It is only myself that can view the reports on report mnanager -
>> whenever anyone else tries to log on and view them they get a blank
>> page -
>> what am i doing wrong?
>>
>> Thanks and HAPPY XMAS!!
>> Greg
>|||Hi Daniel
Yes i think Brian is right - What Chris said helped me out - I had not
previously defined the permissions for my user group. Think yours may be a
separate issue.
Thanks Chris!
Greg
"Brian Welcker [MS]" wrote:
> This is different. In the current version, Report Manager uses a frame to
> access the reports. You probably need to set the ReportServerExternalURL
> property. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/drp_deploying_v1_0h9e.asp.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Daniel Williams" <daniel@.aniworld.com> wrote in message
> news:eabjGuP7EHA.2568@.TK2MSFTNGP10.phx.gbl...
> >
> > I have what may be a similar issue. All users on our local intreanet can
> > view the reports, but people from the outside cannot. They can browse to
> > the page, and see the list of reports, but when they try to view a report,
> > they get "The Page cannot be Displayed".
> >
> > Could this be the same as Greg's problem?
> >
> > Thanks,
> > Daniel Williams
> >
> > "Greg" <Greg@.discussions.microsoft.com> wrote in message
> > news:48094DE4-52C0-4522-A822-352BADD676E4@.microsoft.com...
> >> Hi All,
> >>
> >> I am having some difficulty allowing users to view reports. I have
> >> developed a series of reports on RS and deployed them to Report Manager.
> >> However It is only myself that can view the reports on report mnanager -
> >> whenever anyone else tries to log on and view them they get a blank
> >> page -
> >> what am i doing wrong?
> >>
> >>
> >> Thanks and HAPPY XMAS!!
> >>
> >> Greg
> >>
> >
> >
>
>|||You're right. Our problem was that we did not have a
<ReportServerExternalURL> value, so it was trying to use the internal
address when rendering the report. Completely different issue.
Cheers,
Daniel Williams
"Greg" <Greg@.discussions.microsoft.com> wrote in message
news:70AA6573-50BB-4D9F-BBE3-13E599B6673F@.microsoft.com...
> Hi Daniel
> Yes i think Brian is right - What Chris said helped me out - I had not
> previously defined the permissions for my user group. Think yours may be
> a
> separate issue.
> Thanks Chris!
> Greg
> "Brian Welcker [MS]" wrote:
>> This is different. In the current version, Report Manager uses a frame to
>> access the reports. You probably need to set the ReportServerExternalURL
>> property. See
>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsadmin/htm/drp_deploying_v1_0h9e.asp.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Daniel Williams" <daniel@.aniworld.com> wrote in message
>> news:eabjGuP7EHA.2568@.TK2MSFTNGP10.phx.gbl...
>> >
>> > I have what may be a similar issue. All users on our local intreanet
>> > can
>> > view the reports, but people from the outside cannot. They can browse
>> > to
>> > the page, and see the list of reports, but when they try to view a
>> > report,
>> > they get "The Page cannot be Displayed".
>> >
>> > Could this be the same as Greg's problem?
>> >
>> > Thanks,
>> > Daniel Williams
>> >
>> > "Greg" <Greg@.discussions.microsoft.com> wrote in message
>> > news:48094DE4-52C0-4522-A822-352BADD676E4@.microsoft.com...
>> >> Hi All,
>> >>
>> >> I am having some difficulty allowing users to view reports. I have
>> >> developed a series of reports on RS and deployed them to Report
>> >> Manager.
>> >> However It is only myself that can view the reports on report
>> >> mnanager -
>> >> whenever anyone else tries to log on and view them they get a blank
>> >> page -
>> >> what am i doing wrong?
>> >>
>> >>
>> >> Thanks and HAPPY XMAS!!
>> >>
>> >> Greg
>> >>
>> >
>> >
>>sql

Deploying SQL Server packaged application

I developed an application using C# and SQL Server 2005. I want to
know how to package such an application so that users can download it
from my site, install it and start using it. Previously I have done
this with my VB/MS Access applications. There was no further
requirements as only blank MDB file need to be included. But I don't
know how to package an app. with SQL Server. How to configure the
setup so that on installing SQL Server along with the blank data files
are automatically installed on user's computer.It depends on variuos factors.
1. SQL Server 2005.
If your app uses SQL Server 2005 Sd or up, you probably do not distribute
SQL Server2005 itself. However, if you use SQL Server Express, you may wan
to include both SQL Server Express and the database. If you do want to
include SQL Server Express, your app being server app (such as web app) or
desktop app may make different. For desktop app, do you want to install SQL
Server Express on all user computers (so the data is not shared), or you
only need one SQL Server on the LAN and all users of youe desktop app share
the data (thus, only install one instance of SQL Server somewhere on the
LAN). You can search the net on the topic on packaging SQL Server Express
into installation package. It may not as complicated as imaging, but I never
did it and personally, I'd rather seperate the installation of SQL Server
Express from my application.
2. Database itsellf.
You can use script to create all SQL Server objects (database, views, SPs,
user logins...) during installation;
But simply including clean database file (*.mdf/*.ldf) into the package and
attach it to a ready SQL Server durinig installation would be easier.
However, after attaching database file, you may still need to run some
scripts to perform some user specific tasks, such as set/change SQL Server
login, database user...
"RP" <rpk.general@.gmail.com> wrote in message
news:1192532194.579961.226780@.e34g2000pro.googlegroups.com...
>I developed an application using C# and SQL Server 2005. I want to
> know how to package such an application so that users can download it
> from my site, install it and start using it. Previously I have done
> this with my VB/MS Access applications. There was no further
> requirements as only blank MDB file need to be included. But I don't
> know how to package an app. with SQL Server. How to configure the
> setup so that on installing SQL Server along with the blank data files
> are automatically installed on user's computer.
>

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

deploying report models

Hi Friends
I've quick question ,we have a .net application and we want develop a report builder utility for our end users.I found sql server's built in Report builder is very useful so i created a report model which is easy enough for our end users to select different data items to create their own reports.
My questions is how can i deploy this from our production machine to client site ?what are the requirements etc.,

I have looked web for any references but in vain.
Any help with this regard is much appreciated.
Thank you

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Note that a deployed model contains the DSV definition it is based on inside the smdl document itself. Until you deploy it, the DSV is *not* “plugged” into the smdl file, however….So don’t just create a model using the Model Designer, and then expect to be able to take the resulting smdl and bring it to the client site. You must first deploy it in order to get Visual Studio to merge the DSV into a complete smdl document.

|||Thank you very much Russell.
I understand export and upload options you mentioned but i did not understand your 2nd point. ie

>>You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

so does it mean that uploading SMDL file at the site will not be enough ,will it?
Thanks for your help on this. I really appreciate if you have some sample code or some links abt these deploying issues.

I looked MS help but they just talk abt deploy option from solution explorer and they dont talk abt deploying on completly different machine.|||As long as you've also uploaded the data source that the DSV in your model uses, just upload the model (smdl) and you'll be fine.|||Thanks Russell.That makes sense.

one more question on this one ,i'll upload data source also thats no problem but how can i change connection string easily. as you know sql server names can be different. we've no of client sites where we need to deploy our reporting models. our database name will be same but as you know server names can be different with each site.

can connection string be taken from a config file ?
Thanks for your help|||You can set the connection string for the model manually in Report Manager (or Management Studio). Bring up the model properties and go to the datasource tabs. If you want to do it programmatically / through script, you will want to call SetItemDataSources (http://msdn2.microsoft.com/en-us/library/ms160404(en-US,SQL.90).aspx).|||Thanks Brian
Will give it a try mate|||

Russell Christopher - msft wrote:

Hi --

There are a number of ways to accomplish this:

First, export the model definition (smdl) from the production server using Report Manager.

Then:

- You can bring it to the client site and directly upload it via Report Manager

- You can use the CreateModel() method of the ReportingService2005 web service to programmatically create the model after you load the smdl into an array of bytes

Russel, Why cant i find "Export " option on report manager page ? or am i missing something ?
anyway i managaed to find .smdl file on my machine (where i created the report model) and on production server i used "upload File" to import my report model and it worked fine and users could create reports fine.
but today i changed report model as users needed few more entities and i tried to deploy again the updated report model with same steps as above. But all existing reports created prior to this stopped working!! i get runtime errors.. but users can create new ones though.
am i doing anything wrong ?
Thanks for your help again. am still trying to get my head around on deploy issues.|||

"Export" is actually labeled "Edit" in Report Manager. It's a very small hyperlink on the General property tab of your report.

I'm not exactly sure why the previously published reports stopped working. Did they work after you reset the data source for each report?

|||Thank you very much Russell
finally i successfully did "export" at client machine.it worked nicely.Thanks you so much for you help on this .
BTW the other issue (ie, previous reports not working) i'll need to do little more testing and i'll see what happens if change report model and re-deploy again on the same client machine.|||

Russell,

I'd like some more information on your point 2, using the CreateModel() method. I know how to reference the ReportingServices2005 web service, and then instantiate it's class. Then I can do (for instance) rs.CreateModel(), but how do I load the smdl into an array of bytes? I've not done that. Do you have an online resource that can explain this process?

And I know that you can use Report Viewer in Visual Studio to avoid sending a user to a report server to view reports. Is this also an option with ad hoc reports? If I use the BI to create a Report Model, and then export it, can I now direct our users to a web page/site that I create (that I can have more control over) in order to avoid sending them to the Report Server?

Thanks in advance.

Marvin Hoffman

|||

FileStream stream = File.OpenRead(@."c:\MyReport.smdl");
definition = new Byte[stream.Length];
stream.Read(definition, 0, (int) stream.Length);
stream.Close();
//Now use CreateModel with "defintion" variable

|||

Hi,

This is what i came up with while searching for a way to create model programaticaly.

Uploading a Report Model

Dim DataSourceName As String = "/Adventure Works"

Dim ModelName As String = "/Adventure Works Model"

Dim MyServer As String = "MyReportServer"

Dim rs As New ReportingService2005

rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim props = Nothing

Dim modelPath As String = "./Adventure Works Model.smdl"

Dim fs As FileStream

fs = File.OpenRead(modelPath)

Dim modelDefinition As Byte() = New [Byte](fs.Length) {}

fs.Read(modelDefinition, 0, CInt(fs.Length))

fs.Close()

Try

rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Creating Data Source

Dim dsDefinition As New DataSourceDefinition

dsDefinition.Extension = "OLEDB-MD"

dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated

dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"

dsDefinition.ImpersonateUserSpecified = True

dsDefinition.Enabled = True

dsDefinition.EnabledSpecified = True

Try

rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Associating Report Model with Data Source

Dim ds() As DataSource

ds = rs.GetItemDataSources(ModelName)

Dim dsref As New DataSourceReference

dsref.Reference = DataSourceName

ds(0).Item = dsref

Try

rs.SetItemDataSources("/Adventure Works Model", ds)

Catch e As SoapException

Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +

" (" + e.Detail.Item("Message").InnerText + ")")

End Try

Deploying a website with user authentication - getting errors

I have built a site that uses form-based authentication. Am I correct in assuming that the users and passwords will be stored in the SQLServer database in the aspnetdb.mdf file? The website works great and the authentication works how I want it to. However, I've tried to copy over the file onto a remote machine. The first page is a login screen and this is displayed without any problems but as soon as I attempt to log in I get an error message of:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I'm wondering if this is something to do with the fact I installed SQLServer Express edition on the remote machine. The website works no problem on the local machine in debug mode of Visual Studio 2005 (Professional Edition). I followed instructions to enable remote connections on SQLServer Express from a Microsoft support doc. If anybody can point me in the right direction I'll be very grateful as I've hit a wall here.

Did you check the connection string in web.config if it is pointing to the right server. Also, if you are using aspnetdb.mdf, you should have at least SQL Server Express edition installed on the machine hosting the website.

|||

Hi. Thanks for replying. The only connection string I have in my webconfig file is for an Oracle database that I access elsewhere in the website. Since the original website works in the debug mode of Vis Studio 2005 without any other connection string then I just copied the webconfig file over to the remote server as it was. Does that mean I have to add a connection string to the aspnetdb.mdf file? (The server has SQLServer Express installed on it.)

|||

Have a look at this and try adding a connectionstring to the server.
https://help.maximumasp.com/SmarterTicket/Customer/KBArticle.aspx?articleid=878

You might have to attach the local mdf file using this in the connection string.

AttachDBFilename=|DataDirectory|aspnet.mdf 
connectionStrings> <remove name="LocalSqlServer" /> <add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/></connectionStrings>
 

Deploy Reports to specific folders

Hi, I am a new user for RS so this might be a very dump question for some of the advanced users here.

I am using SQL 2005 RS and was wondering how I can deploy reports to specific folders that I created on the Reports page. I do know that I can go to Properties to move the reports to different folders. But the problem I have is when I deploy the reports from VS 2005 ( I created the reports using VS 2005) they were deployed back to the old/root folder. How can I change the 'deploy-to' destination folder for the reports in VS 2005?

Many Thanks,

Casey

Hi caseycasper,

You could go to the “Project Properties Page” (right click the project)

Set the “TargetReportFolder” property, to the folder you would like to deploy the report to.

Note: if the folder is not available, .NET will create the folder for you J

|||

Thanks JDee.

But what if I want to deploy different reports to different folders? Let's say I have 20 reports, 5 reports each for Team A, Team B, Team C, and Team D, for which I have created report folder. How can I set that up so the reports will go to the specific team folder automatically when I deploy them in VS? I don't want to do the 'move' function everytime I deploy updated reports...

Thanks,

Casey

|||

If I am not wrong that feature is not supported yet.

You could either create different projects for reports which are going to be placed into different foldersL.

Or

Create your own deployment scripts to do it as per your requirement. Please see the link belowJ:

http://msdn2.microsoft.com/en-us/library/ms162839.aspx

|||

Thank you JDee! Appreciate your help!

|||Hi, I also search for that option a few month ago and didn't find this feature.

I think that a good solution is like JDee said - Create a project for every team under the same solution and then configure every project to his own folder.

Good luck.|||

Yup, I just created new projects under the same solution and was able to deploy reports to the "sub -folders".

Thank you all!

Deploy Reports

Hi all,
I am just wondering is it possible to deploy a report to the report server and only specific a group of users can view it (lets say Content Manager role users) without having to log into the report manager using Content Manager account and manually delete the groups that I do not want to show the reports to? By the time I deployed it and log in using Content Manager role, I am afraid other users might log in and browse the reports, which contain confidential report results to certain users.
Anyone have any idea how to go about solving this situation please share it out. Or guide me to a place where I can find the solution for it. Thanks in advance.
Daren

You can set permissions on folders on the report server and then deploy reports to a particular folder based on the permissions you want for each report.

See this link:

http://msdn2.microsoft.com/en-us/library/ms155804.aspx

|||Thanks Ryan.
Daren

deploy permissions

Hi,
I'm trying to deploy reports from the dev. environment and I'm getting the
error:
'The permissions granted to users 'server\user' are insufficient for
performing this operation.'
That is not the user that I am logged onto my development machine,
nor is it a user on the report server. Where can I find the source of
this error. The 'server\user' must be embedded somewhere.
TIA,
MarcDoes your RS server use Basic auth turned on? This could be the user you
are seeing. You might also want to check if you have anonymous access
turned on, if so then every user will seem like the same user.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Marc Miller" <mm1284@.hotmail.com> wrote in message
news:e6wwTdkWFHA.3840@.tk2msftngp13.phx.gbl...
> Hi,
> I'm trying to deploy reports from the dev. environment and I'm getting the
> error:
> 'The permissions granted to users 'server\user' are insufficient for
> performing this operation.'
> That is not the user that I am logged onto my development machine,
> nor is it a user on the report server. Where can I find the source of
> this error. The 'server\user' must be embedded somewhere.
> TIA,
> Marc
>

deploy management studio to users

is there a way to deploy only the workstation tools? Can I create an msi or
sms package to install only the tools I need for users to have? Can I lock
down the interface so all options are greyed out other than workstation tools?
There are several different ways that you can do this all made super easy by
SQL 2005.
I dont need to waste my time writing the steps since someone wrote a really
nice blog...
http://www.databasejournal.com/featu...le.php/3611426
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
Tech Blog - www.technologyis.com
*/
"vision" <vision@.discussions.microsoft.com> wrote in message
news:9A3263B3-DA01-4DF8-B23A-142B6094F095@.microsoft.com...
> is there a way to deploy only the workstation tools? Can I create an msi
> or
> sms package to install only the tools I need for users to have? Can I
> lock
> down the interface so all options are greyed out other than workstation
> tools?
>
|||thanks, I found that and followed the steps. the only thing is it also
installs configuration manager. we only want to install management studio.
I tried removing all except for sql tools and still the same result
thanks for your time.
"Warren Brunk" wrote:

> There are several different ways that you can do this all made super easy by
> SQL 2005.
> I dont need to waste my time writing the steps since someone wrote a really
> nice blog...
> http://www.databasejournal.com/features/mssql/article.php/3611426
>
>
> --
> /*
> Warren Brunk - MCITP - SQL 2005, MCDBA
> www.techintsolutions.com
> Tech Blog - www.technologyis.com
> */
>
> "vision" <vision@.discussions.microsoft.com> wrote in message
> news:9A3263B3-DA01-4DF8-B23A-142B6094F095@.microsoft.com...
>
>

Deploy db to client with users?

I have a db with test data, and a .NET app for it's use. How do I pass
over
users with the db, and how to I get MachineName\ASPNET as a user in that
install?
TIA
__Stephen
See if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...b;en-us;314546
AMB
"Stephen Russell" wrote:

> I have a db with test data, and a .NET app for it's use. How do I pass
> over
> users with the db, and how to I get MachineName\ASPNET as a user in that
> install?
> TIA
> __Stephen
>
>

Deploy db to client with users?

I have a db with test data, and a .NET app for it's use. How do I pass
over
users with the db, and how to I get MachineName\ASPNET as a user in that
install?
TIA
__StephenSee if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/defaul...kb;en-us;314546
AMB
"Stephen Russell" wrote:

> I have a db with test data, and a .NET app for it's use. How do I pass
> over
> users with the db, and how to I get MachineName\ASPNET as a user in that
> install?
> TIA
> __Stephen
>
>

Deploy db to client with users?

I have a db with test data, and a .NET app for it's use. How do I pass
over
users with the db, and how to I get MachineName\ASPNET as a user in that
install?
TIA
__StephenSee if this helps:
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
AMB
"Stephen Russell" wrote:
> I have a db with test data, and a .NET app for it's use. How do I pass
> over
> users with the db, and how to I get MachineName\ASPNET as a user in that
> install?
> TIA
> __Stephen
>
>

Deny view on system tables and views

Hello,
Through a GUI, my users can see all the system table or views.
I want to hide these tables and views so the users cannot see them in the
list.
Is it a good idea to:
use master;
deny select on 'systemTable1' to Public
deny select on 'systemTable2' to Public
deny select on 'systemTable3' to Public
...etc...
deny select on 'systemTablen' to Public
or it can have bad consequences?
ThxTo the best of my knowledge, this isn't supported. Move to 2005, where there is explicit support for
this, and by default you can only see objects you have access to (except for databases, but that can
be changed).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44884b87$0$851$ba4acef3@.news.orange.fr...
> Hello,
> Through a GUI, my users can see all the system table or views.
> I want to hide these tables and views so the users cannot see them in the list.
> Is it a good idea to:
> use master;
> deny select on 'systemTable1' to Public
> deny select on 'systemTable2' to Public
> deny select on 'systemTable3' to Public
> ...etc...
> deny select on 'systemTablen' to Public
> or it can have bad consequences?
> Thx
>

Deny view on system tables and views

Hello,
Through a GUI, my users can see all the system table or views.
I want to hide these tables and views so the users cannot see them in the
list.
Is it a good idea to:
use master;
deny select on 'systemTable1' to Public
deny select on 'systemTable2' to Public
deny select on 'systemTable3' to Public
...etc...
deny select on 'systemTablen' to Public
or it can have bad consequences?
ThxTo the best of my knowledge, this isn't supported. Move to 2005, where there
is explicit support for
this, and by default you can only see objects you have access to (except for
databases, but that can
be changed).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44884b87$0$851$ba4acef3@.news.orange.fr...
> Hello,
> Through a GUI, my users can see all the system table or views.
> I want to hide these tables and views so the users cannot see them in the
list.
> Is it a good idea to:
> use master;
> deny select on 'systemTable1' to Public
> deny select on 'systemTable2' to Public
> deny select on 'systemTable3' to Public
> ...etc...
> deny select on 'systemTablen' to Public
> or it can have bad consequences?
> Thx
>

deny users to see sa login SQL 2005

deny users to see sa login SQL 2005Carlos
If I remember well you can disable 'sa' , check out BOL
"Carlos" <ca@.msn.es> wrote in message
news:%23YEy6fvvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when a new user is created I execute next sentences:
> DENY VIEW ANY DATABASE TO user
> DENY ALTER ANY LOGIN TO user
> The new user cant see the other logins and databases, but he can see the
> "sa" login. Can I change this?
> Thanks
>
|||Thanks Uri,
i've disabled sa and created a new user that belongs to sysadmin. Its
convenient that i change the db owner of the system databases to the new
user?
Thanks!
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:OdgICvvvHHA.4800@.TK2MSFTNGP05.phx.gbl...
> Carlos
> If I remember well you can disable 'sa' , check out BOL
>
> "Carlos" <ca@.msn.es> wrote in message
> news:%23YEy6fvvHHA.3588@.TK2MSFTNGP06.phx.gbl...
>

deny users to see sa login SQL 2005

underprocessableCarlos
If I remember well you can disable 'sa' , check out BOL
"Carlos" <ca@.msn.es> wrote in message
news:%23YEy6fvvHHA.3588@.TK2MSFTNGP06.phx.gbl...
> Hi,
> when a new user is created I execute next sentences:
> DENY VIEW ANY DATABASE TO user
> DENY ALTER ANY LOGIN TO user
> The new user cant see the other logins and databases, but he can see the
> "sa" login. Can I change this?
> Thanks
>|||Thanks Uri,
i've disabled sa and created a new user that belongs to sysadmin. Its
convenient that i change the db owner of the system databases to the new
user?
Thanks!
"Uri Dimant" <urid@.iscar.co.il> escribi en el mensaje
news:OdgICvvvHHA.4800@.TK2MSFTNGP05.phx.gbl...
> Carlos
> If I remember well you can disable 'sa' , check out BOL
>
> "Carlos" <ca@.msn.es> wrote in message
> news:%23YEy6fvvHHA.3588@.TK2MSFTNGP06.phx.gbl...
>

deny truncate?

What if I want to give users Query Analyzer-style access to my data, but
don't want them to delete it?
I can do
deny delete on dbo.mytable to public
which is great... assuming they don't know how to do 'truncate table'.
uh?
is there any way I can prevent users from truncating a table by permissions?Only members of the sysadmin server role and the db_owner and db_ddladmin
database roles and the table owner have permissions to Truncate the table.
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
> permissions?|||> is there any way I can prevent users from truncating a table by
permissions?
Yes, don't make them administrators or dbo.
http://www.aspfaq.com/
(Reverse address to reply.)|||From Books Online:
Permissions
TRUNCATE TABLE permissions default to the table owner, members of the
sysadmin fixed server role, and the db_owner and db_ddladmin fixed database
roles, and are not transferable.
The bottom line: Manage security properly and the users will not have
rights to do anything that you do not want them to.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:86930C7A-AC66-4E96-AB9C-487200CD223D@.microsoft.com...
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by
permissions?|||OK...
We have implemented a rule that any developer that needs to create tables in
a database has to be members of the db_owner role of the database as they
need to be able to create dbo. prefixed tables, as if they can't, then one
will create a table and it will be called, say
mydb.dave.davestable
and then when, say Julie, selects, she will effectively get the results from
mydb.julie.davestable
which either won't exist or else she won't get the same results as dave...
I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.
e.g. I need to create a dbo. procedure, and I need to be able to create
another dbo.procedure, without having to spend time setting users permission
s
from it whenever I create a new procedure.
"Bonj" wrote:
[vbcol=seagreen]
> What if I want to give users Query Analyzer-style access to my data, but
> don't want them to delete it?
> I can do
> deny delete on dbo.mytable to public
> which is great... assuming they don't know how to do 'truncate table'.
> uh?
> is there any way I can prevent users from truncating a table by permissions?[/vbco
l]|||No problem as long as you have separate development and production
environments and the developers are not a member of the db_owner role on the
production environment?
Jacco Schalkwijk
SQL Server MVP
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...[vbcol=seagreen]
> OK...
> We have implemented a rule that any developer that needs to create tables
> in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
> from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
> permissions
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>|||>>I don't need them to be db_owner for anything other than to be create and
run any existing dbo. prefixed SPs and also any future ones aswell without
the future ones being explicitly assigned to them.<<
They do not need to be dbo to execute a dbo owned stored procedure. Just
have dbo issue
GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
You can easily create a stored procedure that issues the appropriate grant
statements to the appropriate users on all stored procedures or on a subset
of stored procedures based on creation date or name. You decide what you
need and create the supporting stored procedure to do it.
Giving users the ability to create tables...I would not give them dbo
rights for this. I would let them play in their own "sandbox" by creating
tables under their account. When their sand castle (or table) has been
approved then dbocan create the table within the development environment (as
dbo).
I have used the same techniques in the past with much success.
Keith
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
> OK...
> We have implemented a rule that any developer that needs to create tables
in
> a database has to be members of the db_owner role of the database as they
> need to be able to create dbo. prefixed tables, as if they can't, then one
> will create a table and it will be called, say
> mydb.dave.davestable
> and then when, say Julie, selects, she will effectively get the results
from
> mydb.julie.davestable
> which either won't exist or else she won't get the same results as dave...
> I don't need them to be db_owner for anything other than to be create and
> run any existing dbo. prefixed SPs and also any future ones aswell without
> the future ones being explicitly assigned to them.
> e.g. I need to create a dbo. procedure, and I need to be able to create
> another dbo.procedure, without having to spend time setting users
permissions[vbcol=seagreen]
> from it whenever I create a new procedure.
>
> "Bonj" wrote:
>
permissions?|||erm.... nah, that wouldn't really work.
You see, the thing is we *do* have separate development and production
environments... slightly (tries to keep a straight face). No, we do.
It's just that most things will need further development after they've been
put into production. In fact, I tend to rate a system as good if it needs
very little time spent developing it after it's been put into production,
although the contrary doesn't necessarily mean it's bad. However, I digress.
To put it diplomatically, when forming an expectation as to how long it will
be before the results of a 'requested improvement' will be seen, the
requester is likely to look on the optimistic end of the scale of how much
testing will be required. Consequently, code changes are made in production
environment - so the opportunity to use the results as real if the 'testing'
all passes, doesn't need to be missed. At the same time, if a production
server is busy with several jobs, other ad-hoc systems but that are
officially 'in production' will overflow onto one of the spare 'development'
servers.
Hence, it makes no sense to *force* a certain server to be either *always*
development or *always* production.
Besides, if a system is in production, the amount of use through Query
Analyzer it gets is less than one that's in development, so people are
unlikely to accidentally truncate the table anyway, but they are when in
development.
I think the most likely cause of the data getting deleted is the database
being recompiled. But then, that's a good thing, as it ensures one run
doesn't inherit the previous one's values. But I could go for miles down
that road - which is at the end of the day a lot like writing a load of
error handling code which might never be called.
but thanks for your help anyway
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23pmvt3TsEHA.1336@.tk2msftngp13.phx.gbl...
> No problem as long as you have separate development and production
> environments and the developers are not a member of the db_owner role on
the
> production environment?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Bonj" <Bonj@.discussions.microsoft.com> wrote in message
> news:F3F68F4F-15C4-44DA-809C-B297C4F1AD84@.microsoft.com...
tables[vbcol=seagreen]
they[vbcol=seagreen]
one[vbcol=seagreen]
dave...[vbcol=seagreen]
and[vbcol=seagreen]
without[vbcol=seagreen]
but[vbcol=seagreen]
>|||> They do not need to be dbo to execute a dbo owned stored procedure. Just
> have dbo issue
> GRANT EXECUTE ON dbo.YourProcName TO DeveloperNameGoesHere
I know, the thing is though, typing out all those statements could be
regarded as work that isn't actually getting anybody anwhere that they
couldn't be just put there by flipping a switch once. There isn't a large
amount of developers, only 5 - in fact there's twice as many servers than
developers. I just thought if it was easy, then I could do it without too
much hassle.

> You can easily create a stored procedure that issues the appropriate grant
> statements to the appropriate users on all stored procedures or on a
subset
> of stored procedures based on creation date or name. You decide what you
> need and create the supporting stored procedure to do it.
That's an option - it does need repeatedly calling, i.e. every time the
database runs, though.

> Giving users the ability to create tables...I would not give them dbo
> rights for this. I would let them play in their own "sandbox" by creating
> tables under their account. When their sand castle (or table) has been
> approved then dbocan create the table within the development environment
(as
> dbo).
Don't get me wrong - I'm not the administrator. I'm a developer.
The 'sandbox' approach wouldn't really work though, because all too often
than not, we have to use the results of what another developer has done,
which is thus in his table. The 'users' of the system, as such, don't even
get to see SQL server. All they see are Excel sheets and graphs, and various
things such as crystal reports in pdf format, that are downloaded off the
servers.
Cheers|||> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once.
Do you use source control? Do you store your stored procedures within
script files within that source control system? If so just add the
appropriate command to the stored procedure script files. Each time the
stored procedure is compiled (created) from the script file the appropriate
grant statement(s) will be executed.

> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
No, it needs to be called every time a stored procedure is dropped and
created (or newly created).
The sandbox approach would only apply for a very short development cycle.
You develop a table and a stored procedure in your sandbox. You get it
working. You get your DBA to create the table and stored procedure(s) as
dbo and you are done.
Keith
"Bonj" <benjtaylor at hotpop d0t com> wrote in message
news:OqrBpjVsEHA.2128@.TK2MSFTNGP11.phx.gbl...
Just[vbcol=seagreen]
> I know, the thing is though, typing out all those statements could be
> regarded as work that isn't actually getting anybody anwhere that they
> couldn't be just put there by flipping a switch once. There isn't a large
> amount of developers, only 5 - in fact there's twice as many servers than
> developers. I just thought if it was easy, then I could do it without too
> much hassle.
>
grant[vbcol=seagreen]
> subset
you[vbcol=seagreen]
> That's an option - it does need repeatedly calling, i.e. every time the
> database runs, though.
>
creating[vbcol=seagreen]
> (as
> Don't get me wrong - I'm not the administrator. I'm a developer.
> The 'sandbox' approach wouldn't really work though, because all too often
> than not, we have to use the results of what another developer has done,
> which is thus in his table. The 'users' of the system, as such, don't even
> get to see SQL server. All they see are Excel sheets and graphs, and
various
> things such as crystal reports in pdf format, that are downloaded off the
> servers.
>
>
>
>
>
> Cheers
>

Deny select to "everyone" except object owner

Hello,
I want to create a table that has denies all select, update, delete
access for all users except the owner of the table. This isn't really
hard to do, however what I'm trying to accomplish is that access is
also denied from the SQL Server Manager, meaning the dbo must also be
denied.
Is this possible? I've tried to deny select access for the object on
the dbo, however this doesn't do anything...
Thanks in advance for any answers.
Best regards,Not possible - you cannot deny any permissions to the owner of the
database (ie. the dbo user within the database). And, as an extension
to that, you cannot deny any permissions in any database to members of
the sysadmin server role as they're implicitly owners of every database.
You can, however, create a login on the server, make it the owner of the
database (ie. the login will map to the dbo user within the database)
and then disable that login so nobody can use it. That would
effectively deny access to anything within the database to the dbo user
since that user would not even be able to connect to the SQL instance.
*mike hodgson*
http://sqlnerd.blogspot.com
Peter wrote:

>Hello,
>I want to create a table that has denies all select, update, delete
>access for all users except the owner of the table. This isn't really
>hard to do, however what I'm trying to accomplish is that access is
>also denied from the SQL Server Manager, meaning the dbo must also be
>denied.
>Is this possible? I've tried to deny select access for the object on
>the dbo, however this doesn't do anything...
>Thanks in advance for any answers.
>
>Best regards,
>
>