Databases
StackOverflow2013
Postsdbo
POHow to implement role-based security in T-SQL?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to implement role-based security in T-SQL?
    primarykey
    data
    text
    <p>I am developing a stored proc for an SSRS 2008 report, but I want to limit the data output according to the role of the user running the report. How do I do this? What I wanted to do was to retrieve the Windows user name and filter the records based on this, but users are using the "sa" account to login to the server. So now I'm thinking I should use the AD login instead. </p> <p>I've also seen how security can be setup in SSRS instead of the T-SQL sproc so that users have access to appropriate folders. But this second approach sounds much more cumbersome since we have many different reports and because I want users to have access to all reports, but just access to different parts of the data.</p> <p>Is it better to implement security via SSRS groups and folders on the report server or else inside the T-SQL sproc?</p> <p>I looked at that link for the "(=User.UserID)" solution, but am having trouble implementing this. What I did: I created a dataset in SSRS by selecting the text query:</p> <pre><code>select people_id from people_rv where last_name like 'd%' </code></pre> <p>This returns a list of people_id's</p> <p>Then, I created a new parameter and set it equal to the above dataset for available values. And I made it Text datatype and allow Null and Blank values. No default values. Finally, I set visibility for the tablix:</p> <pre><code>=iif(Parameters!staff_id.Value in (select people_id from users),1,0) </code></pre> <p>But this generates an error. How can I reformulate this tablix expression?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USsalvationishere
    1. USsalvationishere
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to implement role-based security in T-SQL?
      1. USGabe
      1. VTFavorite
    2. VO
      singulars
      1. POHow to implement role-based security in T-SQL?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFor reference: Under NO condition should the SA password be distributed or used by an application. It is there for one reason: access by a database administrator. Even then I would counsel against using it for any reason. SA is a commonly known username and is the frequent target of dictionary based attacks. Further using it in an application indicates that the app in question has absolute total control over the database server itself: which is bad.
      singulars
      1. POHow to implement role-based security in T-SQL?
      1. USNotMe
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload