SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


permission


permission

Author
Message
bmr270
bmr270
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 398
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_ No Emp_Name Emp_Sal Emp_Comm Emp_Rank
123 xyz $5000 $2000 4


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)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143653 Visits: 18649
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_ No Emp_Name Emp_Sal Emp_Comm Emp_Rank
123 xyz $5000 $2000 4


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
Learn Extended Events

bmr270
bmr270
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 398
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
SQLRNNR
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: General Forum Members
Points: 143653 Visits: 18649
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
Learn Extended Events

bmr270
bmr270
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 398
Yes, I have employee Id's in my Table. How to use them?

/BMR
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103054 Visits: 15045
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
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
bmr270
bmr270
SSChasing Mays
SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)SSChasing Mays (609 reputation)

Group: General Forum Members
Points: 609 Visits: 398
Thanks for reply.

/Mahesh
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search