permission

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » permission

permission

Rate Topic

Display Mode

Topic Options

Author

Message

bmr270

Posted Monday, January 04, 2010 1:51 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:21 PM
Points: 79, Visits: 353

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)

Post #841706

SQLRNNR

Posted Monday, January 04, 2010 2:09 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

bmr270 (1/4/2010)

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #841718

bmr270

Posted Monday, January 04, 2010 2:28 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:21 PM
Points: 79, Visits: 353

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

Post #841731

SQLRNNR

Posted Monday, January 04, 2010 3:07 PM

SSCoach

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:46 PM
Points: 18,732, Visits: 12,329

bmr270 (1/4/2010)

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.

Post #841747

bmr270

Posted Tuesday, January 05, 2010 7:13 AM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:21 PM
Points: 79, Visits: 353

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

/BMR

Post #842065

Jack Corbett

Posted Wednesday, January 06, 2010 9:22 AM

SSChampion

Group: General Forum Members
Last Login: 2 days ago @ 12:22 PM
Points: 10,571, Visits: 11,871

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How 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

Post #842930

bmr270

Posted Wednesday, January 06, 2010 8:42 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 2:21 PM
Points: 79, Visits: 353

Thanks for reply.

/Mahesh

Post #843281

« Prev Topic | Next Topic »

Permissions