Re: Securing Stored Procedures from being seen

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: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server doesn't check permissions on indirectly referenced objects as ... You can prevent ad-hoc execution of powerful master database procs while ... >I have a user who has execute permissions on a store procedure in a>database> which in turns executes 4 stored procedures in the master database. ...
    (microsoft.public.sqlserver.security)
  • Re: Help understanding Stored proc Level Secuirty?
    ... Jasper Smith (SQL Server MVP) ... I set permissions to my Stored Procedures, ... Access to stored procs fail ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005
    ... access for the Visual Studio 2005, when working on ADO.NET (SQL Server ... Permissions are per logins and users. ... permissions can be granted through stored procedures, ...
    (comp.databases.ms-sqlserver)
  • Re: permissions required for executing CDOSys stored procedures
    ... he is by default member of the sysadmin server ... role on the SQL Server database unless steps are taken to prevent that. ... sysadmin and who has not been granted specific execute permissions on the ... it is possible to GRANT EXECUTE ON sp_OACreate TO ...
    (microsoft.public.sqlserver.security)