Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL user in the Report Parameters

Status
Not open for further replies.

Levora

Vendor
Dec 5, 2007
10
PT
Hi,

I have created a Data Source that requires the SQL server authentication, then I would like that the user which is logged can only access to his personal data. So I have created a Report Parameter, but I cannot discover how to put SQL user in the function. (I managed to put the Windows user (=User!UserID), but it’s not the solution in this case, unfortunately)

Thank you very much for your help or ideas.
 
I'm a bit confused....Maybe you can explain a bit more if I have your scenario wrong....

I don't think SQL records anywhere which user created a particular record. It does record who created an object (table, view, sproc, etc). But not the data in the objects.

So if my co-developer and I both entered a record into a table while logged into the SQL server as different users and added records to a table, there would be no way to know who entered each record. Correct me if I am wrong, oh great SQL experts out there. [smile]

Now, if you are recording in a field in a table the logged in user when inserting the records, then you could most definitely use that as a parameter and get back just the records the user entered.

Please explain your scenario a bit more, providing some sample data if you can. I am quite curious as to what you are trying to do....

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer
 
Hi mstrmage1768,

Sorry for explaining my self properly, I’m not an English native speaker. So, what I’m trying to do is:

I have a series of Datasets that have a common Data Source, and this Data Source requires a SQL authentication, and the Report has a Parameter.

I would like that in the Report Parameter I could use the SQL user that is logged in, to make query in the Datasets.

Datasets:
SELECT user, date, value
FROM test
WHERE user=@SQL_USER

Report Parameters:
SQL_USER= ???????????

How can I use the SQL user that is logged in?

Thank you very much for trying to help me, I hope I could explain the project that I’m trying to develop.

Andre
 
That makes much more sense...and was what I was hoping you were trying to do. (BTW - Your English is fine, just the description was not clear.)

You seem like everything is set up right....now just need that last variable piece.

I don't have an answer for you as yet, but I will be digging... [smile]

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer
 
Hi mstrmage1768,

Thank you for your help.

Best regards

Andre
 
I guess if you have the login of the user who made the change then you can use the same piece of information in your report...

create table test (theuser sysname DEFAULT SUSER_SNAME())
insert test default values

report query would then be

select * from test where theuser = suser_sname()

This could still work if you have a table that relates the users 'real' name with their Windows/SQL login name - or then again I might be on the completely wrong track

:)
 
Hi,

When I run the report it requires the SQL authentication, because of the Data Source, so I would like to use the user in the parameter, so that I can only see the data for that user.

I know how to use the Windows user =User!UserID , but I must use the SQL user that is logged in to the data source.

I think that your solution it will requires an extra authentication procedure and an extra table, and the decision maker doesn't like to much authentications ;)

Thank you very much for your help.

André
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top