Securing Stored Procedures from being seen RRS feed

  • Question

  • Hi,

    First, excuse me if I use the wrong terminology.  I am a programmer first and work with databases only occasionally, so I might use the wrong terms.  Second, its my first post and hopefully it's in the right section.  With that being said:

    I'm attempting to write a program (fat client) that allows users to connect to a SQL 2000 DB and get a list of stored procedures (reports) they are allowed to execute.  I do not want them seeing any stored procedures they cannot execute.  The security is intended to be Active Directory groups.  A single user can potentially belong to multiple groups, in multiple domains.

    Example: Two users, userA and userB.  Four groups, domain1\group1, domain1\group2, domain2\groupX, domain2\groupY gX, gY, gZ.  User uA belongs to groups domain1\group1 and domain2\groupX.  User uB belongs to domain1\group2.  No one belongs to domain2\groupY.

    In the database, storedProc1 can be executed by domain1\group1 and domain1\group2, storedProc2 by domain2\groupX, storedProc3 by domain2\groupX, and storedProc4 by domain2\groupY.

    So the results I'd like is for userA to see storedProc1 and storedProc2, and for userB to see storedProc1.  Somehow, SQL Server knows that userA belongs to both groups, so there should be some way of using that information to do what I'd like.

    I've tried using the system tables [syspermissions], [sysusers] and [sysobjects] , which can tell me that groupX has execute permissions on the stored procedure, but I don't see a way of using that information to associate userA with groupX.

    Does anyone have any ideas if this can work?
    Wednesday, July 29, 2009 9:44 PM

All replies

  • Hi,

    First use xp_logininfo 'domainname\loginname','members'

    this will confirm that groups having access to sql server is allowing access to which domain users.

    Once u find out that particular user/login is part of which group you can gather details according to groups access for that login.

    Sachin A.

    Monday, August 3, 2009 11:16 AM