Re: Securing Stored Procedures from being seen


Hi Russell, thanks for answering.

I am aware of the solution you've posted. The issue with why it will not
work is that the user will be logging into SQL Server with their username
using Trusted_Connection=True in the connection string. I will know their
username, but in SQL Server, there are too many users to map an individuals
username to a stored procedure. I will be using an Active Directory group,
granting the group execute permissions. So I cannot use their username to
query sysobjects.

I can use your query, but I would have to know what groups the user belongs
to. So in the "AND PERMISSIONS(id) & 0x20 = 0x20 -- Execute rights" portion
of your where clause, I do not know the (id). SQL Server does some kind of
user to group mapping; can I leverage this?

"Russell Fields" wrote:

Delvin,

SQL Server 2005 and up can have the security setting such that a user cannot
see the names of any stored procedures to which he has no rights. SQL
Server 2000 does not have this feature.

However, since you are writing a fat client to control what they see, you
could use a query like this to get the stored procedures to which they have
access.

SELECT name
FROM sysobjects
WHERE type = 'P' -- Stored Procedure type
AND PERMISSIONS(id) & 0x20 = 0x20 -- Execute rights
ORDER BY name

Here is the 2005 description of the PERMISSIONS function. But I believe it
is identical in 2000. Check your Books Online.

RLF

"DelvinK" <DelvinK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F514EF3F-5856-4B35-8EB8-ED705677BB66@xxxxxxxxxxxxxxxx
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. 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, or if there is a better way?
Preferrably, I would want everything managed at one point so I don't have
to
mess around with config files.


.

Relevant Pages

  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.sqlserver.server)
  • Re: parallelism
    ... SQL Server can not execute multiple stored procedures at ...
    (microsoft.public.sqlserver.programming)
  • Re: Extended stored procedures failues - only under sql server log
    ... I'm talking about "home grown" extended stored procedures. ... For example, 'sa' can execute xp_cmdshell, but CANNOT execute our ... regardless of the fact that its sql server login is ...
    (microsoft.public.sqlserver.programming)
  • Re: Invalid stored procedures are getting created which have errors
    ... one method to validate procs is to execute with ... is to actually execute it. ... > i'm having a strange issue with SQL server 2000. ... > create stored procedures that have critical erros. ...
    (microsoft.public.sqlserver.server)
  • Extreme performance issues (SQL Server 2000/ADO.NET/C#)
    ... This process runs very quickly if run through Query ... same exact stored procedures and views, run in the same exact order, through ... system that runs SQL Server (a 4-cpu Xeons system with 2gigs of physical ... When I execute these steps manually through query analyser,, ...
    (microsoft.public.dotnet.framework.adonet)
Loading