Crystal Reports chart

I have a report That list phone calls that are received by users. These calls are grouped by user, then by Month, week, day and hour. I want to make a chart that shows the calls by day of week (Sunday, Monday, Tuesday, etc) and this chart will be in the footer of the user group ( The chart will show the total calls that were recieved in each day of the week by that user).

The way i have done it was: In the Data tab of the "Chart Expert" option "Placement - Place chart:" i selected the "for each" user group (This is the first group).

In the option "On change of" i put FF_GetWeekday
(a formula field to get the day of the week")

In the option "Show values" i selected the field "table.call_id" to count all the calls that were recieved in each day of the week by each user. This works fine, the problem is that i need to count only the calls that were answered (without the lost calls) by the user and with a call duration greater than 0 seconds.
The chart expert dont have any option to validate the counting of the calls.

I have tried instead of the field "table.call_id", using a formula field with a code something like:
If table.call_duration>0 and table.call_type="Answered" then "{table.call_id}"
but the result is always the correct+1, for example if i have 30 calls in monday for user "USER1" then in the chart appears 31

I dont know what i am doing wrong or if what i am trying to do is possible.

can you help me?

Thank YouSounds to me as if you need to "reset" ( to zero) the value of the formula field ("{table.call_id}") each time you begin a new group, a new user.

Or try a running total where you want to evaluate when
table.call_duration>0 and table.call_type="Answered" and reset on user.

Crystal Reports 9 - end user printing problem

Crystal generates reports based on user parameters from my .asp program. It works fine from all but one users PC. This user prints "chinese" or "garbage" when they hit the print button. It doesn't matter what printer they print to, the screen displays fine, but the report is "garbage." Any suggestions?If it's just the one computer, maybe you need update the printer drivers?

Crystal Reports 8 User session expired error

Hi,
As we are migrating from NT server to Win 2003 server. web applications are moved over from nt as well. I am facing problem with one the web apps which uses crystal reports 8. "User Session expired" error msg is popping out when we try to export the report into any supported format such html, excel, word etc.. The report comes up no issues..however unable to use export option.

I went through the business objects .pdf for user session expired err msg...but nothing helped. i am still stuck with this problem.
can anybody help me here pls.

FYI...It's a ASP/VB web appl using crystal reports 8.0 on Win 2003 machine.

thanks
DRM.Hello,

Further to my earlier msg, after commenting out the default error msg "User Session expired" i got this error msg

"The Report was not Exported. The selected report format may be disabled on the server" actually this is common msg coming up for all export formats...available in crystal 8.0 version.

Therefore, any ideas how to check/verify and Enable/Disable this on server?

ur help on this will be greatly appreciated.

thanks a ton in adv.

Cheers,
muki

Crystal Reports 11 crashes when adding sub-report

I'm a new user of Crystal Reports 11. I'm able to create a new report without trouble. As soon as I try to add a sub-report to this report, CR crashes and this happens everytime. I uninstalled and re-installed CR thrice, but this problem still persists.

I'd really appreciate if anyone could share why this might be happening?Refer support page of this site
www.BusinessObjects.com|||Hi!

I just wanted to tell you, I migrated from cr 10 to 11 yesterday and had a cr 10 report with a subreport in it, wich I opened in cr 11. And it worked well.
Hope it works out for you.
/Helenius

crystal report.. on network application

i m using crystal report with vb...

i have designed a report with a table on it..
the table creates when user click on the preview button on the form..
named tmpAccountLedger..

but when 2 or more users click at the same time..with the different account specifie.d...
the report shows the same for all users..

what should i do..
plz suggest..
regards.What's the underlying database? Can you use a session table? Can you use some sort of id to uniquely name each table creation? I assume you drop it at the end somehow?|||For MSSQL, I use global temporary tables for such purposes. When developing a report, I create a normal "dummy" table with prefix "_tmp_" that can be used for building report. At runtime, I create global temporary table like "##SF93DXU2" with same structure, and replace the name of original dummy table with the name of actual temporary table. Works fine. You can even forget to drop the table afterwards and SQL will clean it up for you. In fact, there are situations where you can't drop the table yourself (for example, if temporary table is used in a subreport at the end of the report, then it is not queried immediately but only when the user reaches the page that contains subreport).

Crystal Report Viewer "Error, Not Implemented"

I have reports to display using Crystal Report 10.

To print it, the user has to push a button on vb which open a vb form called
CrViewer.

Since the source of the CR could be in a different location, im trying to make it load a db from a path that is saved into the db.

Private Sub Form_Load()
Dim lsSql As String
Dim lstr As String
Dim lDateDebut As Date
Dim lDateFin As Date
Dim lcFormulas As String
Dim liListcount As Integer
Dim lsPathBD As String
Dim lvRapportId As Variant
Dim llPeriodePaye As Long

Dim lCrxApplication As CRAXDRT.application
Dim lRapportCrystal As CRAXDRT.Report
Dim lCrxDatabaseField As CRAXDRT.DatabaseFieldDefinition
Dim i As Long

Set lCrxApplication = New CRAXDRT.application

Set lRapportCrystal = lCrxApplication.OpenReport(ObtenirRepertoireRapport() & "RapportAdmRemboursementCumul.rpt", crOpenReportByTempCopy) ' OptenirRepertoirRapport() is a function that return the path to the report directory that is saved in the database.
' Finds the path from the registry...
lsPathBD = GetSetting("GTS", "Demarrage", "RepertoireBD", "")
lRapportCrystal.DiscardSavedData

For i = 1 To lRapportCrystal.Database.Tables.Count
lRapportCrystal.Database.Tables(i).Location = lsPathBD
Next i


frmCRViewer.CRViewer.ReportSource = lRapportCrystal
CRViewer.ViewReport
Set frmCRViewer = Nothing

Set lCrxDatabaseField = Nothing
Set lRapportCrystal = Nothing
Set lCrxApplication = Nothing
End Sub

The problem is encounter when the Crystal Report is opening. it Open but then I get the Error "Erreur, Non Implment" with "Crystal Report Viewer" as title for the errmsg.

Im using Visual Basic 6.0 (SP6)
Windows XP Home, French (SP2)
Crystal Report 10.0.5.1025 Dev. Full, French

Any Help would be Appreciated.the properties of the Database are:

OLE DB (ADO)
Microsoft.Jet.OLEDB.4.0
C:\Projet\Simoneau\DB\DB.mdb

Access
1033
-6
Admin

When using the Crystal Report Tool, no error are popping up but when calling the command
CRViewer.ViewReport The Error pop's up.

I have download a sample from Planet Source

http://www.planetsourcecode.com/vb/scripts/ShowCode.asp?txtCodeId=59818&lngWId=1

It also work fine using the Access/Excel(DAO) database source but if I change it to Microsoft.Jet.OLEDB.4.0 it keep giving me that same error.

Is there anyway to go around this error msg and display my report using Microsoft.Jet or do i have to use Access/Excel DAO absolutely ?

Crystal Report to Default Printer

Hi,
We're writing in Crystal 8.5 and I'd like to know how to direct a report to a user's default printer. A report can be distributed to several locations or printers.
Thanks in advanceYou can make use of CR's printer collection
CR.printer='PrinterName'

crystal report question

Is there a way to have a crystal report prompt the user to add a dollar amount to a report, and have that displayed as a line item?Why not, you can create a numeric parameter value and place it into the Details section just like if it was an ordinary database field. If Crystal sees that this parameter is actually used in report, it will prompt you to enter the corresponding value.

Crystal Report Printing

I m using vb and crystal report. i m calling crystal report through vb i want to give a print selection option to user how can i do this ? pls help me its very very urgent for meCR.EnablePopupMenu = True
CR.EnablePrintButton = True

where CR is the Crystal Report viewer

Crystal Report 8 with Vb 6.0 [parameters screen]

Hi,
I m using crystal in vb 60. and showing all the reports from a folder in the list and when user double click any report it ask for parameter which i fine

but if user click on the cancel button on paramter screen then it should exit but still it open the crystal report with blank page.

How i can stop this if user click on cancel button or can i can hide the cancel button on the parameter screen?

Thanks In advanceTry posting this question in the Crystal Reports Forum (http://www.dev-archive.com/forum/forumdisplay.php?s=&forumid=64)|||[ Moved to Crystal Report forum ]

Crystal Report 10

I have crystal report 10 and programmatically insert reports. right now I am try to Programmatically uncheck the check box for "Prompt the user for database logon when viewing"
please help. thanks!set CR.Enablepopupmenu=false

Cryatal Viewer

I need to test if the user had clicked the print button on the crystal viewer in my design (not in run time) this button is disable how can I by code know if the user had clicked this button on any else button of the crystal reportLet me get this straight...

You want to determine if the Crystal Viewer was clicked in design time

Do you want to add custome events to any of those controls
I can't see why you want to do this, they already have functionality, they already function as they should :confused:

Or..

You can in any case loop through the Form's controls, determine if the current control is a
CrystalDecisions.Windows.Forms.PageView then loop further through that container to (for example) change the caption of the Tabpage.

Can you explain your problem a bit more please!|||I need to know when he clicked the print button I need to know if he choosed to print as Landscape or portrait

crosstab?

I've made "crosstab" queries in Access - it this doable in sql'
My table has:
ID
TestNumber
Score
Each user can have 1-3 records, one for each of the 3 tests.
I need my output to look like this:
ID Test1 Test2 Test3
--
1 score1 score2 score3
2 score1
3 score1 score2
(not everyone will have taken the 2nd or 3rd test at the same time; score#
just indicates the testscore)
'
Thanks for any help or pointers.
-RYes, but it must be statically defined like so:
Select Id
, Min(Case When TestNumber = 1 Then Score End) As Test1
, Min(Case When TestNumber = 2 Then Score End) As Test2
, Min(Case When TestNumber = 3 Then Score End) As Test3
From TableName
Group By Id
Thomas
"r" <r@.r.com> wrote in message news:uB0xq6%23XFHA.2768@.tk2msftngp13.phx.gbl...d">
> I've made "crosstab" queries in Access - it this doable in sql'
> My table has:
> ID
> TestNumber
> Score
> Each user can have 1-3 records, one for each of the 3 tests.
> I need my output to look like this:
> ID Test1 Test2 Test3
> --
> 1 score1 score2 score3
> 2 score1
> 3 score1 score2
> (not everyone will have taken the 2nd or 3rd test at the same time; score#
> just indicates the testscore)
> '
> Thanks for any help or pointers.
> -R
>
>|||SELECT id,
SUM(CASE WHEN testnumber = 1 THEN score END) AS test1,
SUM(CASE WHEN testnumber = 2 THEN score END) AS test2,
SUM(CASE WHEN testnumber = 3 THEN score END) AS test3
FROM YourTable
GROUP BY id
David Portas
SQL Server MVP
--|||If you ever need to go beyond xtab 101 check out
the powerful RAC utility.Similar in concept to Access xtab
but goes way beyond with its options and features.Also
includes other functionality (ie. ranking options) made easy.
www.rac4sql.net

Cross Tab query in SqlServer

Hi,
I have three tables with there fields given in brackets:
User: [userId as int] (PK), [userName as navarchar]
Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
DivisionsOfUsers: [userId as int],[divisionId as int]
the "DivisionsOfUsers" tables has many-to-many relationships between
userid and divisionId.
I would like to generate a result something like this:
Division1 Division2 Division3
User1 1 0 0
User2 0 0 1
User3 1 1 0
User4 0 0 0
and so on...
where "1" indicates that the given User-Division combination exists and
"0" denotes that it doesnt in the "DivisionOfUsers" table.
I have tried all sorts of joins to get this data. But was unable to do
this.
I have been told that this is possible by a cross-tab query. I dont
know how to generate this query.
Can anybody give me a solution for this to be used in Sqlserver 2000 as
well as Sqlserver 2005.
Thanks in advanceOften, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>|||Please post DDL, Sample Data, Expected Results (based on your Sample Data).
In the meantime, you might look up the PIVOT operator in SQL 2005.
"Darsin" <darsin@.gmail.com> wrote in message
news:1164563679.582175.136260@.h54g2000cwb.googlegroups.com...
> Hi,
> I have three tables with there fields given in brackets:
> User: [userId as int] (PK), [userName as navarchar]
> Divisions: [divisionId as int] (PK), [divisionName as nvarchar]
> DivisionsOfUsers: [userId as int],[divisionId as int]
> the "DivisionsOfUsers" tables has many-to-many relationships between
> userid and divisionId.
> I would like to generate a result something like this:
> Division1 Division2 Division3
> User1 1 0 0
> User2 0 0 1
> User3 1 1 0
> User4 0 0 0
> and so on...
> where "1" indicates that the given User-Division combination exists and
> "0" denotes that it doesnt in the "DivisionOfUsers" table.
> I have tried all sorts of joins to get this data. But was unable to do
> this.
> I have been told that this is possible by a cross-tab query. I dont
> know how to generate this query.
> Can anybody give me a solution for this to be used in Sqlserver 2000 as
> well as Sqlserver 2005.
> Thanks in advance
>

Cross Server Communication Issue

We are having a problem getting the security to properly authenticate
cross-server logins where the user is logged in using windows
authentication. We are currently running SQL Server on a W2K3 cluster in a
W2K3 domain. There are actually four servers, two physical, the cluster
virtual server, and the SQL virtual server. We'll call them SQL1P
(primary), SQL1F (failover), SQL (cluster virtual server), and SQLV(SQL
virtual server). All servers have been set up for delegation in AD for
kerberos, and the sql service account has all of the proper permissions.
The problem now is that cross server communication with the other SQL
servers (not in the cluster) will work using standard SQL accounts, but not
using windows authentication. Has anyone else had this problem and if so
how did you resolve it? Thanks in advance for your answers.
What error are you getting when you try to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||This is the query that is being run:
select * from odewhsevprod.master.dbo.sysdatabases
Here is the error we are receiving:
Server: Msg 18452, Level 14, State 1, Line 1 Login failed for user '(null)'.
Reason: Not associated with a trusted SQL Server connection.
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:4H#0BElTEHA.3328@.cpmsftngxa10.phx.gbl...
> What error are you getting when you try to connect?
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||We fixed the issue. We basically went in and reset the SPN's on the server
names and the sqlservice account as well as the mscs account, and now it
works fine.
"-DB" <server.team@.ode.state.or.us> wrote in message
news:eMYaAFjTEHA.2944@.tk2msftngp13.phx.gbl...
> We are having a problem getting the security to properly authenticate
> cross-server logins where the user is logged in using windows
> authentication. We are currently running SQL Server on a W2K3 cluster in
a
> W2K3 domain. There are actually four servers, two physical, the cluster
> virtual server, and the SQL virtual server. We'll call them SQL1P
> (primary), SQL1F (failover), SQL (cluster virtual server), and SQLV(SQL
> virtual server). All servers have been set up for delegation in AD for
> kerberos, and the sql service account has all of the proper permissions.
> The problem now is that cross server communication with the other SQL
> servers (not in the cluster) will work using standard SQL accounts, but
not
> using windows authentication. Has anyone else had this problem and if so
> how did you resolve it? Thanks in advance for your answers.
>

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
ScottHello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegroups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:
>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
> >Is there a cross reference of User Name to Login Name in SQL Server
> >somewhere? In its system tables? It doesn't appear so? The user
> >table for a database doesn't contain the login name.
> >Does anybody know if this cross reference is accessible via system
> >tables?
> >Scott
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/library/system.environment_properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
> > How you get the login names for database users depends on
> > what version of SQL Server.
> > If this is just to check a particular database user, you can
> > execute sp_help_user 'YourUser' and it will return the user
> > name in the database as well as the login name at the server
> > level.
> > If you just want to know how the tables are related, for SQL
> > Server 2000 the syslogins and sysusers tables are related by
> > SID.
> > If you are on 2005, look at sys.server_principals and
> > sys.database_principals.
> > -Sue
> > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > wrote:
> > >Is there a cross reference of User Name to Login Name in SQL Server
> > >somewhere? In its system tables? It doesn't appear so? The user
> > >table for a database doesn't contain the login name.
> > >Does anybody know if this cross reference is accessible via system
> > >tables?
> > >Scott
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/library/system.environment_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> > On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> > > How you get the login names for database users depends on
> > > what version of SQL Server.
> > > If this is just to check a particular database user, you can
> > > execute sp_help_user 'YourUser' and it will return the user
> > > name in the database as well as the login name at the server
> > > level.
> > > If you just want to know how the tables are related, for SQL
> > > Server 2000 the syslogins and sysusers tables are related by
> > > SID.
> > > If you are on 2005, look at sys.server_principals and
> > > sys.database_principals.
> > > -Sue
> > > On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> > > wrote:
> > > >Is there a cross reference of User Name to Login Name in SQL Server
> > > >somewhere? In its system tables? It doesn't appear so? The user
> > > >table for a database doesn't contain the login name.
> > > >Does anybody know if this cross reference is accessible via system
> > > >tables?
> > > >Scott
> > sp_help_user info is just what I need, but I'm having trouble
> > executing it from .aspx page. I'll keep trying.- Hide quoted text -
> > - Show quoted text -
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
Scott
Hello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegr oups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>
|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:

>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott
|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
>
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.
|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/library/system.environment_properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
>
>
>
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -
|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:[vbcol=seagreen]
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/library/system.environment_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
>
>
>
>
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross reference of User Name to Login Name

Is there a cross reference of User Name to Login Name in SQL Server
somewhere? In its system tables? It doesn't appear so? The user
table for a database doesn't contain the login name.
Does anybody know if this cross reference is accessible via system
tables?
ScottHello,
There should be definely mapping between Login and User. A user can not be
created with out a Login. This chain will not be there if you restore a
database from a diferent SQL Server machine. In this case the appropriate
Login will not be there for the database user. To recreate the login chain
you can use SP_CHANGE_USERS_LOGIN system stored procedure. See books online
for its usage.
Thanks
Hari
"scotty" <scasti1@.cox.net> wrote in message
news:1175554544.489300.144210@.d57g2000hsg.googlegroups.com...
> Is there a cross reference of User Name to Login Name in SQL Server
> somewhere? In its system tables? It doesn't appear so? The user
> table for a database doesn't contain the login name.
> Does anybody know if this cross reference is accessible via system
> tables?
> Scott
>|||How you get the login names for database users depends on
what version of SQL Server.
If this is just to check a particular database user, you can
execute sp_help_user 'YourUser' and it will return the user
name in the database as well as the login name at the server
level.
If you just want to know how the tables are related, for SQL
Server 2000 the syslogins and sysusers tables are related by
SID.
If you are on 2005, look at sys.server_principals and
sys.database_principals.
-Sue
On 2 Apr 2007 15:55:44 -0700, "scotty" <scasti1@.cox.net>
wrote:

>Is there a cross reference of User Name to Login Name in SQL Server
>somewhere? In its system tables? It doesn't appear so? The user
>table for a database doesn't contain the login name.
>Does anybody know if this cross reference is accessible via system
>tables?
>Scott|||On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:[vbcol=seagreen]
> How you get the login names for database users depends on
> what version of SQL Server.
> If this is just to check a particular database user, you can
> execute sp_help_user 'YourUser' and it will return the user
> name in the database as well as the login name at the server
> level.
> If you just want to know how the tables are related, for SQL
> Server 2000 the syslogins and sysusers tables are related by
> SID.
> If you are on 2005, look at sys.server_principals and
> sys.database_principals.
> -Sue
> On 2 Apr 2007 15:55:44 -0700, "scotty" <scas...@.cox.net>
> wrote:
>
>
>
sp_help_user info is just what I need, but I'm having trouble
executing it from .aspx page. I'll keep trying.|||I'm somewhat new to ASP and SQL Server, but in the applications that
I've designed the connection to the SQL Server was through a connetion
string and logging in as a generic user name that we called ASP_User.
Therefore sp_help_user would not return the information that you are
looking for.
I think you're looking for information that would be contained on the
client side in the Environment variables.
System.Environment Properties:
http://msdn2.microsoft.com/en-us/li...properties.aspx
Cheers,
Jason Lepack
On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
> On Apr 2, 6:57 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
>
>
>
>
>
>
>
>
> sp_help_user info is just what I need, but I'm having trouble
> executing it from .aspx page. I'll keep trying.- Hide quoted text -
> - Show quoted text -|||On Apr 3, 9:55 am, "Jason Lepack" <jlep...@.gmail.com> wrote:[vbcol=seagreen]
> I'm somewhat new to ASP and SQL Server, but in the applications that
> I've designed the connection to the SQL Server was through a connetion
> string and logging in as a generic user name that we called ASP_User.
> Therefore sp_help_user would not return the information that you are
> looking for.
> I think you're looking for information that would be contained on the
> client side in the Environment variables.
> System.Environment Properties:http://msdn2.microsoft.com/en-us/li...nt_propertie...
> Cheers,
> Jason Lepack
> On Apr 3, 11:45 am, "scotty" <scas...@.cox.net> wrote:
>
>
>
>
>
>
>
>
>
The data rows returned when running sp_help_user in
query_analyzer have data that would be useful in a web application.
For instance, If you could attach a user to a custom application role
in the database, then you could have a user log in with their
user_name, run the procedure, and know what application role that
specific user starts out with, because I think its associated with
their groupname, unless I'm mistaken.
The return view has a GroupName column. Can I access this value? I
run EXEC dbo.sp_helpuser 'user' in the analyzer.
I think I can successfully run the stored procedure from .aspx but I
don't think the sp_help_user procedure was defined to return these
view column values. Apologies for bad terminology I may be using.
One of the defined parameters for that procedure is @.RETURN_VALUE(int,
Return Value)? Want the GroupName column.

Cross Domain access

Hello,
I need to allow user outside the domain to access reporting services reports.
Can you please let me know how I need to configure reporting Services for
this? and what user accounts should be set up?
thank you
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1tarana h via SQLMonster.com wrote:
> Hello,
> I need to allow user outside the domain to access reporting services reports.
> Can you please let me know how I need to configure reporting Services for
> this? and what user accounts should be set up?
> thank you
>
the only way i guess is to extend RS with custom authentication extension.

Cross Database Activation Security

I have two databases Basket_ODS and Intelligence_ODS.

I created a user in the Basket_ODS and Intelligence_ODS databases as follows:

USE Basket_ods

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*******'

CREATE USER BasketServiceUser WITHOUT LOGIN

ALTER AUTHORIZATION ON SERVICE::[Order Send] TO BasketServiceUser

GRANT CONTROL ON SERVICE::[Order Send]

TO BasketServiceUser

CREATE CERTIFICATE BasketServiceCertPriv

AUTHORIZATION BasketServiceUser

WITH SUBJECT = 'ForBasketService'

BACKUP CERTIFICATE BasketServiceCertPriv

TO FILE = 'BasketServiceCertPub'

In the other database...

I created the following:

USE Intelligence_ODS

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '************

USE Intelligence_ODS

GO

CREATE USER BasketServiceUser WITHOUT LOGIN

CREATE CERTIFICATE BasketServiceCertPub

AUTHORIZATION BasketServiceUser

FROM FILE = 'BasketServiceCertPub'

My Queue is in BASKET_ODS and is set up as:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BasketServiceUser'

)

I have performed the following grants in Basket_ODS

grant execute on ODS.ProcessOrderQueue to BasketServiceUser

ProcessOrderQueue calls [ODS].[MoveOrderTotals_Core] in the Intelligence_ODS database.

grant execute on [ODS].[MoveOrderTotals_Core] to BasketServiceUser

ProcessOrderQueue proc is set as follows:

ALTER procedure [ODS].[ProcessOrderQueue]

WITH EXECUTE AS 'BasketServiceUser'

[ODS].[MoveOrderTotals_Core] is set up as follows:

when I run ProcessOrderQueue I get an error message:

ALTER procedure [ODS].[MoveOrderTotals_Core](@.Orderid uniqueidentifier)

with execute as 'BasketServiceUser'

I just don't understand when I run ProcessOrderQueue I get the following error message (when database trust is turned off)

The server principal "sa" is not able to access the database "Intelligence_ODS" under the current security context.

Can you help me figure out what I'm doing wrong. I've spent so much time on this security stuff. Is there another way to do this that is more straight forward without using database trust?

Something key that I left out...

ProcessOrderQueue calls [Basket_ODS].[ODS].GenericProcessor which is defined as follows:

ALTER Procedure [ODS].[GenericProcessor](@.procName nvarchar(128), @.orderID uniqueidentifier)

WITH EXECUTE AS 'BasketServiceUser'

as

DECLARE @.sql nvarchar(4000)

Select @.sql = 'exec ' + @.procName + ' @.orderID'

print @.sql

EXEC sp_executesql @.sql,

-- PARAMETER DECLARATIONS

N'@.orderID uniqueidentifier',

-- PARAMETER VALUES

@.orderID

This procedure is called based on the message read from the queue by ProcessOrderQueue. It then looks up what stored procedure needs to be called as per the message and then calls GenericProcessor with the name of the stored procedure to call along with the associated OrderID. When I changed this proc from 'Execute as OWNER' to Execute as 'BasketServiceUser' I now get the following error message :

The server principal "S-1-9-3-3317550566-1317506277-4110005931-648375857." is not able to access the database "Intelligence_ODS" under the current security context.

The stored procedure [Intelligence_ODS].[ODS].[MoveOrderTotals_Core] does an Insert into [Intelligence_ODS].... Select from [Basket_ODS]. That is the cross database portion.

|||

In order to get the cross database EXECUTE AS context to work, the key ingredient is the signing of the procedure. I see this is missing from you example. In code signing, is really irelevant who owns the certificate in [Basket_ods]. The important thing is for this certificate to sign the procedure that attempts to call another procedure cross database (I think is the [ODS].[ProcessOrderQueue]):

ADD SIGNATURE TO OBJECT::[ODS].[ProcessOrderQueue] BY CERTIFICATE [BasketServiceCertPriv]

Note that once the signature is added, the procedure should not be altered in any way, or the signature will be lost.

In database Intelligence_ODS you have to create a user derived from the [BasketServiceCertPub] certificate and grant him the appropiate permissions, including AUTHENTICATE:

CREATE USER [BasketServiceCertPubUser] FROM CERTIFICATE [BasketServiceCertPub];

GRANT AUTHENTICATE TO [BasketServiceCertPubUser];

GRANT EXECUTE ON [MoveOrderTotals_Core] TO [BasketServiceCertPubUser];

This should be it. The EXECUTE AS clauses on the queue activation, as well as on the [ProcessOrderQueue] procedure, should be probably OWNER. The procedure [MoveOrderTotals_Core] does not need an EXECUTE AS.

I think you are mixing some concepts related to the process of setting up dialog security (users w/o login, certificate ownership) with the concepts related to EXECUTE AS and code signing procedures. The two concepts are very distinct. The EXECUTE AS relies on signing the procedures and users derived from certificates to achieve it's goals.

HTH,
~ Remus