permission

Question

permission

bmr270

Default port

Points: 1477
More actions
January 4, 2010 at 1:51 pm

#219062

Enter Employee No: 123(Report Parameter) (By default, his/her employee_no for a team member, but manager can enter any team member Employee No and the manager can see details of all)
Employee Detail Report:
Emp_ NoEmp_Name Emp_SalEmp_CommEmp_Rank
123xyz$5000$20004
If a manager opens the report, the manager can enter any team member ‘s EmployeeNo and the manager can see the details.
If a team member opens the report, It should show his/her details only.(Means he/she should not have permission to see other’s details)

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

bmr270 (1/4/2010)
Enter Employee No: 123(Report Parameter) (By default, his/her employee_no for a team member, but manager can enter any team member Employee No and the manager can see details of all)
Employee Detail Report:
Emp_ NoEmp_Name Emp_SalEmp_CommEmp_Rank
123xyz$5000$20004
If a manager opens the report, the manager can enter any team member ‘s EmployeeNo and the manager can see the details.
If a team member opens the report, It should show his/her details only.(Means he/she should not have permission to see other’s details)

If I have interpreted your statements correctly, you are looking for suggestions on how to do this?

Assuming that is the means of the post, what comes to mind is a permissions control table that is joined in your queries.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

bmr270 Default port Points: 1477 More actions · Answer 2

If a manager opens the report, the report should allow the manager to give the parameter value for Emp_no parameter and it should display details based on the Emp_no.

If a team member opens my report, By default the “Emp_no” parameter should take the team member(who has opened) emp_no and then the team member can see his/her details only.

I did not join any permission control table.

How shall I do this?

/BMR

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

bmr270 (1/4/2010)
If a manager opens the report, the report should allow the manager to give the parameter value for Emp_no parameter and it should display details based on the Emp_no.
If a team member opens my report, By default the “Emp_no” parameter should take the team member(who has opened) emp_no and then the team member can see his/her details only.
I did not join any permission control table.
How shall I do this?
/BMR

What I envision for this is a lookup table that has the employee and any combination of employee ids that person may view from the report. A manager would have all of the employees under him/her, and any other employee would only have themselves listed in the table. It may also be a better idea to use a select list that only displays the uids for that person that are listed in the table.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

bmr270 Default port Points: 1477 More actions · Answer 4

Yes, I have employee Id's in my Table. How to use them?

/BMR

Jack Corbett SSC Guru Points: 184393 More actions · Answer 5

There are some posts on the duplicate thread found here. I'll be adding new posts to this thread.

Is the employee number parameter a text box or a drop-down list? IF you convert it to a dropdown list you populate via a dataset. In the dataset you do something like:

DECLARE @login VARCHAR(256)

/*

This first value is a manager

*/

SET @login = 'adventure-works\ken0'

/*

this is not a manager

Set @login = 'adventure-works\rob0'

*/

;WITH cteEmpIds AS

(

SELECT

EmployeeID

FROM

HumanResources.Employee

WHERE

LoginID = @login

UNION ALL

SELECT

E.EmployeeID

FROM

HumanResources.Employee E JOIN

cteEmpIds CE ON

E.managerId = CE.EmployeeId

)

SELECT

EmployeeID

FROM

cteEmpIds

You wouldn't Declare the variable that was just so I could make sure it works. You could use the UserID function in SSRS to populate the query parameter. Then your dropdown list would only show the valid values for that user.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

bmr270 Default port Points: 1477 More actions · Answer 6

bmr270

Default port

Points: 1477

January 6, 2010 at 8:42 pm

#1099995

Thanks for reply.

/Mahesh