ssrs 2008 using windows authenication

Question

ssrs 2008 using windows authenication

wendy elizabeth

SSCoach

Points: 16915
More actions
December 1, 2016 at 8:24 am

#401145

When executing an ssrs 2008 report, I would like to ask the following question(s) in regards to obtaining the userid value when utilizing windows authenication:
1. Does the sql always need to be a stored procedure? Can it be inline sql, a view, and/or a function? Can you show me the code and/or point me to url(s) links on how to obtain the userid?
2. The report I want to generate does not use login/userid. It uses a unique identifier in the database. Thus to make the user id access work correctly, I am thinking I would need to create a table that would show the following for each row in the table:
a. domain name/login id, and
b. the corresponding userid that is in the database
,correct? If so, would this code need to be part of a stored procedure and/or can it be inline sql?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

1) SSRS can return any data you really ask it to. It can be the entire contents of Table, a Stored procedure, or SQL. If you're running SQL in the dataset, it can literally be anything you want, provided the user account running it has permissions. You could CREATE a Table, INSERT some data into it. DROP a stored procedure, and change a users permissions. Just remember that only the FIRST set of data in the SQL will be read by SSRS.

2) If I'm understanding correctly you want to only display results for the User (let's say MYDOMAIN/JSMITH) however, in your data, his Userid is a ID/GUID (Let's say 1C64D3CB-0266-4AB9-B4F4-8AA62FAF22E4). You can either do that in the SP that returns the data, within the FROM clause (using a JOIN), or get the GUID from one dataset, which then stores that in a hidden parameter, that then passes it to your other dataset.

Let's go with the first option first, that would mean would either have an SP than runs the following, or SQL within your dataset similar to:

SELECT MD.*

FROM MyData MD

JOIN Users U ON MD.UserGUID = U.UserGUID

WHERE U.UserID = @user-id; --UserID has a value of 'MYDOMAIN/JSMITH'

/*

This returns all rows for UserGUID '1C64D3CB-0266-4AB9-B4F4-8AA62FAF22E4' in the table My Data.

The SQL knows that this is for MYDOMAIN/JSMITH, as there is an entry for them in the Users Table.

*/

Your second option would be more along the lines of this:

--Your first SP/SQL DAtaset

SELECT U.UserGUID

FROM Users U

WHERE U.UserID = @user-id; --UserID has a value of 'MYDOMAIN/JSMITH'

--You use that UserGUID to set a default value on your Hidden parameter UserGUID

SELECT *

FROM MyData MD

WHERE MD.UserGUID = @UserGUID; --UserGUID has a value of ''1C64D3CB-0266-4AB9-B4F4-8AA62FAF22E4''

Am I on the right path here, and does that help?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

You definitely are on the correct path! However if I want domain user testdomain/testuser to have access that has been user within the entire database for that user, like database id = 890, then I would need to have the following kind of data setup in a view or table like:

domainname&username/database unique identifier for the user, then the values would need to look like:

domainname&username/database unique identifier for the user

1. testdomain/testuser / 890

2. testdomain/testuser2/ 998

pietlinden SSC Guru Points: 63417 More actions · Answer 3

" However if I want domain user testdomain/testuser to have access that has been user within the entire database for that user " ???

You can create users and groups in Windows and grant the groups permission to execute your report... so I'm confused about your question.

Thom A SSC Guru Points: 99126 More actions · Answer 4

wendy elizabeth (12/1/2016)
You definitely are on the correct path! However if I want domain user testdomain/testuser to have access that has been user within the entire database for that user, like database id = 890, then I would need to have the following kind of data setup in a view or table like:
domainname&username/database unique identifier for the user, then the values would need to look like:
domainname&username/database unique identifier for the user
1. testdomain/testuser / 890
2. testdomain/testuser2/ 998

I'm afraid you lost me here as well. I'm really not sure what you're trying to get at there.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

So you want to fetch the unique identifier for the user that's running the report? This relies on users running the report using their own Windows credentials. You need Kerberos in place for that, otherwise you'll fall victim to the double-hop issue[/url].DECLARE @DBUID int

SELECT @DBUID = [database unique identifier for the user]

FROM MyTable

WHERE [domainname&username] = SUSER_NAME()

John

wendy elizabeth SSCoach Points: 16915 More actions · Answer 6

The following comment was made "You can create users and groups in Windows and grant the groups permission to execute your report... ".

Can you tell me how you create groups in Windows? Are you referring to active directory groups?

pietlinden SSC Guru Points: 63417 More actions · Answer 7

If you are using Windows authentication, then security is handled by Active Directory. You create users and then add them to Groups, and assign the permissions to the groups...