Re: Securing Stored Procedures from being seen

This worked, thanks! I think my issue was that the query I was issuing was
cached and so was not returning the correct results. Anyways, it works now,
nice solution.

"Russell Fields" wrote:

Delvin,

PERMISSIONS works for the current user, whoever he may be, and however his
rights were granted. It 'knows' what groups you are a member of.

RLF

"DelvinK" <DelvinK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9502F842-4197-4FC2-95A6-ADCF7F1E3E84@xxxxxxxxxxxxxxxx
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

  • Re: Wanted: Discussion on MSSQL Internals Interview Qustions
    ... Developer for Microsoft's SQL Server team? ... Other inputs the query optimizer uses are the schema ... The C# dataset caching can only cache results from a single query. ... I don't consider these alternatives to stored procedures, ...
    (microsoft.public.sqlserver.programming)
  • 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: SQL Server
    ... Where is the processing done in a pass through query? ... One of the SQL Server programmers is pushing stored procedures, ... pass-through queries. ...
    (microsoft.public.access.queries)
  • 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)
Loading