Crystal Running Group Total equivilent in Report Services

I'm trying to use Reporting Services Developer edition with SQL Server 2000
and Visual Studio 2003. Net to create a report that already existed in
Crystal Reports 8.5 so that the report can be distributed via email and other
things that Report Services allows.
The Crystal Report uses something called a 'Running Total' field that
summarizes a field and evaluates a value for each chance of the field and
resets the value on the change of a group.
Who would you go about solving this problem in Report Services?
Thanks,
JimCheck out the RunningValue value function in BOL...
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> I'm trying to use Reporting Services Developer edition with SQL Server
> 2000
> and Visual Studio 2003. Net to create a report that already existed in
> Crystal Reports 8.5 so that the report can be distributed via email and
> other
> things that Report Services allows.
> The Crystal Report uses something called a 'Running Total' field that
> summarizes a field and evaluates a value for each chance of the field and
> resets the value on the change of a group.
> Who would you go about solving this problem in Report Services?
> Thanks,
> Jim|||What is BOL and how do you get access to it?
"Jim" wrote:
> Check out the RunningValue value function in BOL...
> "Jim" <Jim@.discussions.microsoft.com> wrote in message
> news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> > I'm trying to use Reporting Services Developer edition with SQL Server
> > 2000
> > and Visual Studio 2003. Net to create a report that already existed in
> > Crystal Reports 8.5 so that the report can be distributed via email and
> > other
> > things that Report Services allows.
> >
> > The Crystal Report uses something called a 'Running Total' field that
> > summarizes a field and evaluates a value for each chance of the field and
> > resets the value on the change of a group.
> >
> > Who would you go about solving this problem in Report Services?
> >
> > Thanks,
> > Jim
>
>|||BOL is Reporting Services Books On Line.
You can also find them on MSDN:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_1l6b.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jim" <Jim@.discussions.microsoft.com> wrote in message
news:74ABD5D6-72D0-43E5-97C7-BD433A7F6876@.microsoft.com...
> What is BOL and how do you get access to it?
> "Jim" wrote:
> > Check out the RunningValue value function in BOL...
> >
> > "Jim" <Jim@.discussions.microsoft.com> wrote in message
> > news:8DDF9D20-B55F-4DD1-8B69-937AD2ACD859@.microsoft.com...
> > > I'm trying to use Reporting Services Developer edition with SQL Server
> > > 2000
> > > and Visual Studio 2003. Net to create a report that already existed in
> > > Crystal Reports 8.5 so that the report can be distributed via email
and
> > > other
> > > things that Report Services allows.
> > >
> > > The Crystal Report uses something called a 'Running Total' field that
> > > summarizes a field and evaluates a value for each chance of the field
and
> > > resets the value on the change of a group.
> > >
> > > Who would you go about solving this problem in Report Services?
> > >
> > > Thanks,
> > > Jim
> >
> >
> >

Crystal reports grouping time stamps by hour

I made a report in crystal reports and its cross tabbed with Times on top, what i need to do is be able to group the times by hour but I have no clue how to do that

right now it looks like this:

8:01 8:11 8:15 8:45 9:15 9:34 and so on

it needs to be like this
8:00 9:00 and so on having everything in the 8-9 range under 8 and 9-10 under 9 and so on

I am using a Ms sql database and taking info from a number field numbers are in the format 800 for 8 oclock 930 for 9 thirty and so on

how would I group them like thisI'm not sure whether this folg is a right solution. but it'll work.

Try to create a formula and check the numeric value. Form a group using code
Like

If {table.field} in [800 to 830] then
1
else if {table.field} in [831 to 900] then
2
etc

Then group the report using this formula. To display the time interval create one more formula. Refer the previous formula here

i.e
if {@.prev} = 1 then
"8.00 - 8.30"
etc

Hope this will work.|||Thanx I will try that out,

Crystal Reports Group By and MySQL mediumtext field

Hello Friends,

I have connected Crystal Reports to MySQL database 4.x.
Now I want to run a query using the group by option on a mediumtext data type field. But as Crystal Reports does not allow to do a Group by on a mediumtext field is there any way i can convert the field to String and then do a group by ?

Thanks for your help in advance.
Shanker.Do you have unique id value for that column?
If so group by itsql

Crystal Report Report Footer help

Hello,

I just joined this site and new to the crystal report. I build the attached report with 2 group footer and want to add Grand Total in report footer but its not working. My group footer is calculated field, and when i try to sum it the calculated field doesn't show under the report fields,any help please?You cannot use summary functions on already summarized fields..|||What other way I can get the grand total?|||Repeat in report footer section the same calculations you do at group footer section but with grand total data

Crystal Report Group by Formula

Hi,

I am a novice and any help would be much appreciated.

The database I am using is as below:

A/C No. Name Payments

1 Brown 10.00
1 Brown 25.00
1 Brown 40.00
2 Jones 100.00
2 Jones 150.00
2 Jones 175.00
2 Jones 56.00
3 Smith 65.00

I have grouped the report by account number. I have then created a formula that categorises the customer based on their payments. I then wish to group customers by these categories but Crystal will not allow me to do so.

The best I can do at the moment is export the initial report to Excel and then create a new report in Crystal based on this.

Can anyone point me in the right direction?

Thanks

Andyyou can group by using formula
can u write the formula, so that i can check it.

crystal report 9

how to select only the last row of a group in crystal report 9<moved thread>

Crystal Report 8.0, Summary function !

Friends,

I'm working with crystal report 8.0 and SQL server.

I need to create a formula in group section, which should return the summary value of next group.

Or is there any function for get summary value if the group value is 'x'. same as SumIF( , , ) in excel.You need to write a formula having the code

If groupvalue='x' then
{field}

and use the summary using this

Crystal Design

Hi,
I've a standard Invoice template with Page header, Group Header and Detail section. Invoice can be of one page or multiple pages depending on the number of invoice line items. I want to print Customer service and payment details (fixed text of about 40 lines) on the back of every page. How do I achieve this. Any help is much appreciated.

thanks & regards
radhastart a new page after group footer. if ur invoice is of one page , then this new page can be printed on back.|||Thanks for this. It works fine when the invoice has one page.
My report is grouped by Invoice Number so for an Invoice with multiple pages group footer prints on the last page of the invoice and the balnk page prints after the last page of the Invoice. My requirement is to print the details on the back of each page. Any help would be much appreciated.

thanks & regards

radha

Cross-tab Row Title

CR XI posted a work-around/solution for inserting a row title in a cross tab report by creating a separate "fake" group, overlaying it and copying the cross tab to the fake group's section. It also discussed horizontal pages.

How do you repeat the label on subsequent/multiple pages though?See if you find answer here

http://support.businessobjects.com/

'Crosspost' Structure a New App (SQLServer C# - Security & Db Connecctions)

Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
--Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.

> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.

> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.

> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.

> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.

> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.

> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?

> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
--|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.

> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
--
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx

'Crosspost' Structure a New App (SQLServer C# - Security & Db Connecctions)

Hi Folks - first off, apologies for the cross post but I'm not really sure
which group would be most appropriate for these questions - no offence
intended!
Porting an app from MS Access to VS with C# and SQLServer, I've come across
a few design challenges that are new to me.
Here's what I had before in MSAccess:
==========================
Frontend / Backend, using User/Group security. Security MDW resides on
Server with the Backend.
App (frontend) has a table to allow user to connect to correct backend -
assuming he has necessary permission to do so (based on the form to make the
connection and the .mdw file permissions).
Each of my client installations will usually have multiple users, connecting
to multiple databases (backends), each backend being a separate business
unit for the Client. Some users have access to all Db's and some to only one
or two. When a user logs in to the app - if there is no current backend link
then he is prompted to go to the connect form and browse for the backend
using a common dialog box. The selected backend is then linked to the app
and the current database name (backend) shown in the status bar.
Client DBA's / Network Manager's have access to the backend to copy / paste
/ move etc. They are not expected to manage the database.
One secure table in each backend has a single column with the number of
licenses the Client has purchased for the business unit and the app code
counts down the number of concurrent users from that number and blocks
further connections until there is a license available (the app checks for
activity on each connected frontend every 5 mins - and releases the license
if none).
Using the above structure (backend / frontend, license and .mdw) I can cater
for this format.
Moving to VS & SQLServer:
==========================
It seems this will be tricky now in SQLServer! I have tables and code to
ensure security, so users are limited to specific menu selections.
Challenge is (right now) three fold -
1. Since the app (C#) needs a connection string before it can see any of
these security / set-up tables, then there needs to be a current connection
string in place. On a newly installed app how can that be - the app doesn't
know where the SQLServer is?
2. We really don't want users to have to set config files etc, so initial
start-up and connection to the various business units Db's needs to be
automated / made click and selectable.
3. We really don't want the Client DBA's to have access to the tables in the
Db (backends). Obviously they would be able to set license and user
permissions if they do.
==========================
Trying to understand these challenges, my questions are:
a. Would it be appropriate to have a separate (secure) file to hold the
initial location of the SQLServer, then have a browse to the actual Db in
SQLServer - then have the app build the connection string?
b. Is it possible to browse the Dbs in a SQLServer, from an app, so the user
can select the correct one to connect to?
c. What type of file would be most appropriate for this (SQLExpress /
MSAccess/Encrypted XML etc)?
d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
access it but the Client DBA could not (to hold license info etc)?
e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could access
it but the Client DBA could not (to hold all the app info and data etc)?
f. If I used SQLExpress as a standalone server (for the entire system or
just for these start-up tables), would that impact tremendously on the
performance of Client network systems if they already had instances of
SQLServer / Express installed for other purposes?
This initial connection (and Db swapping) must be a challenge for others
too - how do you deal with this conundrum in your apps?
Appreciate any feedback.
Kahuna
Kahuna (none@.gonewest.com) writes:
> a. Would it be appropriate to have a separate (secure) file to hold the
> initial location of the SQLServer, then have a browse to the actual Db in
> SQLServer - then have the app build the connection string?
Don't really know why this would be a secure file. In our application
we prompt the user for the server and database. But there are also
situations when users needs to log into a different server. (Test a
new version, report database etc.) If you want to save the users from
the hassle of selecting a server, you could read this from a config
file. I think that could used be a plain text file.

> b. Is it possible to browse the Dbs in a SQLServer, from an app, so the
> user can select the correct one to connect to?
Once you are connected you can select the available databases in the
server. While "SELECT name FORM sys.databases" is simple, it will list
all databases, even if the user has no access to them. You could
check all databases for access, but with many databases on the server
this could be expensive. An alternative is to have a master application
for the app, where you have a table with user-database connections. But
then you will also will need to find a way to maintain this table
reliably, so it does not goes out of sync with the actual database
permissions.

> d. Is it possible to 'Secure' a single table in a SQLServer Db so I could
> access it but the Client DBA could not (to hold license info etc)?
Depends. If the Client DBA, or someone else at the site have admin
privileges in Windows it gets difficult. You could remove
BUILTIN\Administrators from the server, and keep the sa password to
yourself. But as long as they can access the file, they can always
stop the server, attach it to another instance, fiddle with the
table, detach it again, and the start your instance.
Then again, I can't but see that you must have the same problem with
your Access solution today as well.

> e. Is it possible to 'Secure' a single 'Db' in a SQLServer so I could
> access it but the Client DBA could not (to hold all the app info and
> data etc)?
See above.
I should add that while you cannot technically prevent the client staff
from accessing the database, by removing BUILTIN\Administrators you
can set up signs that says "NO TRESPASSING" making it clear that if
they fiddle with the database, they are violating the license agreement.
Provided that you have this covered in the license agreement, that is.

> f. If I used SQLExpress as a standalone server (for the entire system or
> just for these start-up tables), would that impact tremendously on the
> performance of Client network systems if they already had instances of
> SQLServer / Express installed for other purposes?
Not really sure what your concern is. But if there are other SQL Server
instances on the same machine, and you don't set max server memory for
the instances, the server can compete about memory on the machine,
and thus interfer with each other.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Thanks for the feedback Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> Don't really know why this would be a secure file. In our application
> we prompt the user for the server and database. But there are also
> situations when users needs to log into a different server. (Test a
> new version, report database etc.) If you want to save the users from
> the hassle of selecting a server, you could read this from a config
> file. I think that could used be a plain text file.
>
You're right Erland - this woldnt need to be secure - though I guess we'd
need a copy of that file in the same dir as the app (frontend) so it wouldnt
need to find it! But I guess if thats were the case then we'd be as well
creating the entire connection strings in that file and using it to allow
the user to change Db's. Thats would then need to be deployed with the front
end and re-deployed if the Server was moved.

> Once you are connected you can select the available databases in the
> server. While "SELECT name FORM sys.databases" is simple, it will list
> all databases, even if the user has no access to them. You could
> check all databases for access, but with many databases on the server
> this could be expensive. An alternative is to have a master application
> for the app, where you have a table with user-database connections. But
> then you will also will need to find a way to maintain this table
> reliably, so it does not goes out of sync with the actual database
> permissions.
Could use a prefix just on our Db names of course to make it easy to find in
that instance.

> Depends. If the Client DBA, or someone else at the site have admin
> privileges in Windows it gets difficult. You could remove
> BUILTIN\Administrators from the server, and keep the sa password to
> yourself. But as long as they can access the file, they can always
> stop the server, attach it to another instance, fiddle with the
> table, detach it again, and the start your instance.
> Then again, I can't but see that you must have the same problem with
> your Access solution today as well.
No I'm able to remove admin rights to the Access Db's but with a Client
instal of SQLServer - I dont see that he'll be too happy if I were to remove
his rights!!! Or did I miss the poit Erland?

> See above.
> I should add that while you cannot technically prevent the client staff
> from accessing the database, by removing BUILTIN\Administrators you
> can set up signs that says "NO TRESPASSING" making it clear that if
> they fiddle with the database, they are violating the license agreement.
> Provided that you have this covered in the license agreement, that is.
>
> Not really sure what your concern is. But if there are other SQL Server
> instances on the same machine, and you don't set max server memory for
> the instances, the server can compete about memory on the machine,
> and thus interfer with each other.
>
This is looking more and more like I will need to have an instance of a
SQLServer (probably express) installed, that only we can access, and with
only our admin rights - is this possible Erland - can we install a Server
that the Client DBA could not get into?
Even with a config file we need to have a secure table someplace to record
licensed access (concurrent users), so a locked server or file seems like
the only possibility really?
Kahuna
|||Kahuna (none@.gonewest.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9A16A6079EECEYazorman@.127.0.0.1...
> Could use a prefix just on our Db names of course to make it easy to
> find in that instance.
I got the impression that different users were permitted in different
databases. If all users have access to all databases, it's a little
easier.

> No I'm able to remove admin rights to the Access Db's but with a Client
> instal of SQLServer - I dont see that he'll be too happy if I were to
> remove his rights!!! Or did I miss the poit Erland?
>...
> This is looking more and more like I will need to have an instance of a
> SQLServer (probably express) installed, that only we can access, and with
> only our admin rights - is this possible Erland - can we install a Server
> that the Client DBA could not get into?
It all boils down to who is the system administrator for the machine.
It's not clear from your posts where the client machines are located
and who administer them. If you are an application provider and
administer the boxes, then you should have no problems in restricting
where you clients may go.
But if the boxes are located at the client sites, and the clients are
responsible for their administration, hardware etc, then there is no
way you can lock them out, be that Access or SQL Server. The only way
you can keep them out is that you agree to be the system administrator
for the machines. (You say above that you remove admin rights for the
Access file. Yes, you can do that. But if the client is the sysadmin
on these boxes, he can add those permissions back at any time.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
Boxes are Client's, at Client's sites.
Using User/Group security, through an .mdw file, I don't believe there is
any way for a sysadmin to gain access without my explicit permissions in an
MSAccess .mdb file Erland!
Kahuna
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9A16C6DC4F16FYazorman@.127.0.0.1...
> Kahuna (none@.gonewest.com) writes:
> I got the impression that different users were permitted in different
> databases. If all users have access to all databases, it's a little
> easier.
>
> It all boils down to who is the system administrator for the machine.
> It's not clear from your posts where the client machines are located
> and who administer them. If you are an application provider and
> administer the boxes, then you should have no problems in restricting
> where you clients may go.
> But if the boxes are located at the client sites, and the clients are
> responsible for their administration, hardware etc, then there is no
> way you can lock them out, be that Access or SQL Server. The only way
> you can keep them out is that you agree to be the system administrator
> for the machines. (You say above that you remove admin rights for the
> Access file. Yes, you can do that. But if the client is the sysadmin
> on these boxes, he can add those permissions back at any time.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Cross-database Ownership Chaining in SQL 2005

Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample database--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test I got the data return.
But if I try to call a stored procedure to access a table in a new user database I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2 table are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks. Even I
disable this option at server and database level it still works for AdventureWorks database. Can you do this simple test on your SQL 2005 and post your result?
Thanks for any help!
Bill
In order for the cross-database chain to be unbroken for schema owned by
'dbo' users, the databases need to be owned by the same login. The msdb
system database is owned by 'sa' so I would expect your test to work if you
change the owner of your 'pubs' database to 'sa' and turn on the DB_CHAINING
database option:
ALTER AUTHORIZATION ON DATABASE::pubs
TO sa
ALTER DATABASE pubs
SET DB_CHAINING ON
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:%23BO$hZ82FHA.3136@.TK2MSFTNGP09.phx.gbl...
Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup
now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample
database--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test
I got the data return.
But if I try to call a stored procedure to access a table in a new user
database I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2
table are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks.
Even I
disable this option at server and database level it still works for
AdventureWorks database. Can you do this simple test on your SQL 2005 and
post your result?
Thanks for any help!
Bill

Cross-database Ownership Chaining in SQL 2005

Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup
now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample databa
se--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test
I got the data return.
But if I try to call a stored procedure to access a table in a new user data
base I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2 t
able are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks.
Even I
disable this option at server and database level it still works for Adventur
eWorks database. Can you do this simple test on your SQL 2005 and post your
result?
Thanks for any help!
BillIn order for the cross-database chain to be unbroken for schema owned by
'dbo' users, the databases need to be owned by the same login. The msdb
system database is owned by 'sa' so I would expect your test to work if you
change the owner of your 'pubs' database to 'sa' and turn on the DB_CHAINING
database option:
ALTER AUTHORIZATION ON DATABASE::pubs
TO sa
ALTER DATABASE pubs
SET DB_CHAINING ON
Hope this helps.
Dan Guzman
SQL Server MVP
"Bill Wang" <wdw2130833@.hotmail.com> wrote in message
news:%23BO$hZ82FHA.3136@.TK2MSFTNGP09.phx.gbl...
Hi,
Sorry to post in this group. It seems only few people in SQL 2005 newsgroup
now.
I met a problem in SQL SERVER 2005 SEPTEMBER CTP. The option "Cross
Database Ownership Chaining" won't work in SQL 2005 Step CTP. But there is
no problem if I call stored procedure to access a table in the sample
database--AdventureWorks. for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from AdventureWorks.Person.Address
--select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is a normal account with minimal permission. When I called sp_my_test
I got the data return.
But if I try to call a stored procedure to access a table in a new user
database I got error: for
example:
use msdb
go
drop proc sp_my_test
go
create proc sp_my_test
as
select * from pubs.dbo.test2
go
grant exec on sp_my_test to wdw
go
"wdw" is the same account with minimal permission. Pubs database and test2
table are created by sa account.
When I call sp_my_test I met permission denied error:
The error is:
Msg 229, Level 14, State 5, Procedure my_test, Line 4
SELECT permission denied on object 'test2', database 'pubs', schema 'dbo'.
I changed the server and database option for Cross Database Ownership
Chaining. Neither works in my database but it DOES work for AdventureWorks.
Even I
disable this option at server and database level it still works for
AdventureWorks database. Can you do this simple test on your SQL 2005 and
post your result?
Thanks for any help!
Bill

Cross Database Permission

I have a stored procedure that I created on one database with permission
granted to a select group of users. The proc grabs information from another
database on the same server. When users with permissions to the proc try to
run it, they get permission errors saying that they do not have permissions
to access the other database that is referenced in the proc. It was my
understanding that when you run a proc, it runs under the context of the proc
creator (in this case dbo), otherwise you have to grant select, insert,
update, etc. on all tables that your proc references. Does this not work
cross database?Clark,
You do not mention which version of SQL Server you are using, but both 2000
and 2005 have in the Books Online index an entry for "cross-database
permissions" which you should read.
Basically, the ownership-chain is broken at a database boundary as I quote:
"Cross-database permissions are not allowed; permissions can be granted only
to users in the current database for objects and statements in the current
database. If a user needs permissions to objects in another database, create
the user account in the other database, or grant the user account access to
the other database, as well as the current database."
There is a setting to turn on "cross database ownership chains", but that is
not recommended due to the (potentially serious) security side-effects.
RLF
"Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
>I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from
> another
> database on the same server. When users with permissions to the proc try
> to
> run it, they get permission errors saying that they do not have
> permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the
> proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?|||It is running on SQL Server 2000 with the allow cross-database ownership
chaining disabled. So I take it dbo does not have cross database access,
which makes sense now that I think about. Thanks.
"Russell Fields" wrote:
> Clark,
> You do not mention which version of SQL Server you are using, but both 2000
> and 2005 have in the Books Online index an entry for "cross-database
> permissions" which you should read.
> Basically, the ownership-chain is broken at a database boundary as I quote:
> "Cross-database permissions are not allowed; permissions can be granted only
> to users in the current database for objects and statements in the current
> database. If a user needs permissions to objects in another database, create
> the user account in the other database, or grant the user account access to
> the other database, as well as the current database."
> There is a setting to turn on "cross database ownership chains", but that is
> not recommended due to the (potentially serious) security side-effects.
> RLF
> "Clark Kent" <ClarkKent@.discussions.microsoft.com> wrote in message
> news:92FF4E5C-9690-41AE-A4EA-362018397F79@.microsoft.com...
> >I have a stored procedure that I created on one database with permission
> > granted to a select group of users. The proc grabs information from
> > another
> > database on the same server. When users with permissions to the proc try
> > to
> > run it, they get permission errors saying that they do not have
> > permissions
> > to access the other database that is referenced in the proc. It was my
> > understanding that when you run a proc, it runs under the context of the
> > proc
> > creator (in this case dbo), otherwise you have to grant select, insert,
> > update, etc. on all tables that your proc references. Does this not work
> > cross database?
>
>|||Clark Kent,
1 - Cross-Database Ownership Chaining should be enabled
2 - If the owner of the sp is the owner of the objects being referenced from
the other db, then there should be no problem, but if the owner of the sp
just has rights to "select", then SS will check if the one executing the sp
also has "select" right on those objects.
Ownership Chains
http://msdn2.microsoft.com/en-us/library/ms188676.aspx
SQL Server 2005 Security Overview for Database Administrators
download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/SQLSecurityOverviewforAdmins.doc
Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
AMB
"Clark Kent" wrote:
> I have a stored procedure that I created on one database with permission
> granted to a select group of users. The proc grabs information from another
> database on the same server. When users with permissions to the proc try to
> run it, they get permission errors saying that they do not have permissions
> to access the other database that is referenced in the proc. It was my
> understanding that when you run a proc, it runs under the context of the proc
> creator (in this case dbo), otherwise you have to grant select, insert,
> update, etc. on all tables that your proc references. Does this not work
> cross database?