Results 1 to 5 of 5

Thread: User

  1. #1
    Join Date
    Sep 2005
    Posts
    313

    Unanswered: User

    Hi,

    When I make the Application role active, the User function will be changed to Application name. How can I retrieve the current user name (Window network user id)?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by accessman2
    Hi,

    When I make the Application role active, the User function will be changed to Application name. How can I retrieve the current user name (Window network user id)?
    Pass it in from the client?

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2005
    Posts
    313
    No, I want to create the View,

    create View as
    select * from tbl1 where [Username] = User

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    you want to create a view for every user of your app? uh, no.

    that doesn't scale well does it? any time you think you need to add another object for another user/customer/etc, stop and rethink your design.

    adding a user should mean you add a row to a table somewhere, not an entirely new object.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by jezemine
    you want to create a view for every user of your app? uh, no.

    that doesn't scale well does it? any time you think you need to add another object for another user/customer/etc, stop and rethink your design.

    adding a user should mean you add a row to a table somewhere, not an entirely new object.
    @jezemine
    I think he wants to create one view which limits records returned based on the system user name of the logged in user. Thus, he doesn't need to create an object for each user in the db; just one object which behaves differently for each user.

    Be careful in your approach to this; it's a pretty inflexible (what if one user suddenly inherits another user's territory and now needs to be able to see records for more than one identity?).

    Consider using a stored procedure (which accepts a userid as a parameter) and consider using a "permissions" table to proxy the user id to another identity value. It's not too hard to build an inline function that returns a set of values that the user has permission to see based on some identity:

    Code:
    spMyData (@myID int)
    
    AS
    
    SELECT 
    *
    FROM
       dbo.myTable
    WHERE 
       MyProxyID in (SELECT ProxyID FROM dbo.udfPermissions(@myID))
    
    
    
    FUNCTION udfPermissions (@myID int)
    RETURNS @Proxy TABLE (ProxyID int)
    
    AS
    
    INSERT @Proxy (MyProxyID)
    SELECT MyProxyID 
    FROM dbo.Permissions
    WHERE MyID = @MyID
    In any case, if you are using Application roles in SQL, you will need to derive your UserID from the client or from some other means.


    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •