Securing Stored Procedures from being seen

  • Hi everyone,

    I'm trying to figure out a way (if possible) of allowing certain users to see certain objects (in this case, stored procedures). I want it tailored so they can only see a partial list, and another user can see a different partial list (in this case, users are members of an Active Directory group).

    Is there a way to do this in SQL Server 2000? If so, how?

    Thanks in advance

    Edit: For clarity I will explain exactly what I intend to do. I want to create a program that has no connection information in it. It will try to connect to the database with whatever the user is currently logged in with. From there, it will generate a list of stored procedures that user can see (based on a usergroup). The user can then execute any of the stored procedures they are able to see to generate a report. What I am struggling with is auto-populating a list of stored procedures based on what the user can execute. Does this make sense?

  • I haven't tested this but I think you could just put the different stored procedures into different schemas and then assign permissions to each group by schema.

  • Are schema's available in SQL Server 2000? Forgive me, but I'm a programmer first and only occasionally work with databases. My terminology may not be up to snuff. When you say schema are you talking about creating a database table and putting the names of the stored procedures within?

    I've had some success in using the system tables

    [syspermissions]

    [sysusers]

    [sysobjects]

    However, the program would have to know what group the user belongs to, which means writing code to query active directory. This is a possible solution, but SQL Server already knows what group the user belongs to... is there an easier way?

  • Sorry, I don't have a SQL 2000 to test with. It is harder to do in 2000 but schemas would be the owner. You have objects (tables, views, stored procedures) that are named dbo.tableName or dbo.storedProcedure. In these cases dbo would be the schema. I am not sure that you can grant permissions like this but you could create the appropriate users and then change the owner of the objects to be that user.

    So lets say I have a stored procedure named dbo.test

    I can create a user in the database called developer and then change the owner of the dbo.test stored procedure to developer.

    I don't know what implications this might have and as I said I don't have a 2000 instance to test on. You could try looking up sp_changeowner or sp_changeobjectowner in books online.

    Sorry I can't help more.

  • Hi,

    Thanks for the reply Matt. I have come across the function PERMISSIONS when left blank, returns the user's permissions. I don't understand it yet, and am trying a query like so:

    SELECT *

    FROM sysobjects

    WHERE type = 'P' -- Stored Procedure type

    AND PERMISSIONS() & 0x20 = 0x20 -- Execute rights...?

    ORDER BY name

    This is not returning the correct results. I have created a test user, 'test'. I have disabled execute rights explicitly for this user on a stored procedure. When I run the above query I still see all the stored procedures. Looks promising, no results so far.

  • SELECT *

    FROM sysobjects so

    WHERE type = 'P' -- Stored Procedure type

    AND PERMISSIONS(so.id) & 32 = 32 -- Execute rights...?

    ORDER BY name

    You need to specify what object you are checking the permissions on. Instead of using the hex form I opted for the decimal. This worked when I tested it.

    Let me know how this works for you.

  • Ok, what I did worked, but it looked like it didn't work because I think the results of the query were cached. I didn't change anything and all of a sudden the query started working. Thanks Matt for your help!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply