Determine security access to stored procedure through ASP

17 Feb 2006 4:33 PM

webJose

I have an ASP application running in a MS Windows Server 2003 computer
joined to a Windows 2000 Active Directory domain.

Different users have different roles, and the security in the SQL
database is based on Active Directory security groups (SQL server is
configured for Windows security and not SQL server security).

I would like to make certain ASP page show an icon depending on whether
the user has EXECUTE permission on a particular stored procedure.

Example: User X@domain.com is a member of the Active Directory
security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
in of a SQL user called "App Admins", and App Admins have been granted
EXECUTE permission to the stored procedure spDeleteSomething. I want
the ASP to determine if X@domain.com has the permission on
spDeleteSomething so an icon is displayed; in this case it should be
displayed.

I hope I made this clear, but if not, feel free to ask for more details.

17 Feb 2006 5:03 PM

Mark Williams

You can use the IS_MEMBER function to check to see if the current login is a
member of the App Admins group:

SELECT IS_MEMBER('domain.com\App Admins')

returns 1 if the current login is a member of the App Admins security group,
0 if they aren't.

Show quote

"webJose" wrote:

> I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
>
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
>
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
>
> Example: User X@domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
>
> I hope I made this clear, but if not, feel free to ask for more details.
>
>

17 Feb 2006 5:56 PM

JT

In ASP.NET, you can determine if a web user is a member of an Active
Directory group or role without going through SQL Server.

How To: Use Role Manager in ASP.NET 2.0
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000013.asp

For example:

        if (Roles.IsUserInRole("TestRole"))
        {
            Label1.Text = User.Identity.Name + " is in role TestRole";
        }
        else
        {
            Label1.Text = User.Identity.Name + " is NOT in role TestRole";
        }

Show quote

"webJose" <webJ***@gmail.com> wrote in message
news:1140194004.035980.226530@g14g2000cwa.googlegroups.com...
>I have an ASP application running in a MS Windows Server 2003 computer
> joined to a Windows 2000 Active Directory domain.
>
> Different users have different roles, and the security in the SQL
> database is based on Active Directory security groups (SQL server is
> configured for Windows security and not SQL server security).
>
> I would like to make certain ASP page show an icon depending on whether
> the user has EXECUTE permission on a particular stored procedure.
>
> Example: User X@domain.com is a member of the Active Directory
> security group APP-ADMIN. On the SQL server side, APP-ADMIN is the log
> in of a SQL user called "App Admins", and App Admins have been granted
> EXECUTE permission to the stored procedure spDeleteSomething. I want
> the ASP to determine if X@domain.com has the permission on
> spDeleteSomething so an icon is displayed; in this case it should be
> displayed.
>
> I hope I made this clear, but if not, feel free to ask for more details.
>

17 Feb 2006 6:23 PM

webJose

JT: Thank you for your response. Although highly enlighting, I am not
using .NET (I know! I should be). :-)

Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
create user-defined functions to encapsulate this functionality. Now,
out of curiosity, is there a way to test for EXECUTE permissions on any
stored procedure like on the fly? For example, something like:

If CanExecute("spSomeSP") Then
Response.Write "You got it!"
End If

And CanExecute() would test somehow the permissions for the user ID on
the sp name passed as argument.

17 Feb 2006 7:16 PM

Mark Williams

You could query it from the sysprotects system table.

For SQL 2000:

IF EXISTS (
SELECT 1 FROM sysprotects
WHERE [id] = OBJECT_ID('yourproc')
AND [uid] = USER_ID()
AND [action] = 224
AND [protecttype] IN (204,205)
)
BEGIN
PRINT 'You have access'
END

For SQL 2005

IF EXISTS (
SELECT 1 FROM sys.database_permissions
WHERE [class] = 1
AND [major_id] = OBJECT_ID('yourproc')
AND [grantee_principal_id] = USER_ID()
AND [type] = 'EX'
AND [state] IN ('G','W')
)
BEGIN
PRINT 'You have access'
END

--

Show quote

"webJose" wrote:

> JT: Thank you for your response. Although highly enlighting, I am not
> using .NET (I know! I should be). :-)
>
> Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
> create user-defined functions to encapsulate this functionality. Now,
> out of curiosity, is there a way to test for EXECUTE permissions on any
> stored procedure like on the fly? For example, something like:
>
> If CanExecute("spSomeSP") Then
> Response.Write "You got it!"
> End If
>
> And CanExecute() would test somehow the permissions for the user ID on
> the sp name passed as argument.
>
>

17 Feb 2006 7:31 PM

Mark Williams

One caveat to this: the will only return that the user has access if the user
they map too was explicity given access to execute procedure. If their
permission is inherited from membership in a server or database role, my
script will not show them as having access.

If you are explicitly giving execute permission to each of the users, then
it will work.

Show quote

"Mark Williams" wrote:

> You could query it from the sysprotects system table.
>
> For SQL 2000:
>
> IF EXISTS (
> SELECT 1 FROM sysprotects
> WHERE [id] = OBJECT_ID('yourproc')
> AND [uid] = USER_ID()
> AND [action] = 224
> AND [protecttype] IN (204,205)
> )
> BEGIN
>   PRINT 'You have access'
> END
>
> For SQL 2005
>
> IF EXISTS (
> SELECT 1 FROM sys.database_permissions
> WHERE [class] = 1
> AND [major_id] = OBJECT_ID('yourproc')
> AND [grantee_principal_id] = USER_ID()
> AND [type] = 'EX'
> AND [state] IN ('G','W')
> )
> BEGIN
>   PRINT 'You have access'
> END
>
> --
> "webJose" wrote:
>
> > JT: Thank you for your response. Although highly enlighting, I am not
> > using .NET (I know! I should be). :-)
> >
> > Mark: Thank you for your response. IS_MEMBER workS OK for me. I'll
> > create user-defined functions to encapsulate this functionality. Now,
> > out of curiosity, is there a way to test for EXECUTE permissions on any
> > stored procedure like on the fly? For example, something like:
> >
> > If CanExecute("spSomeSP") Then
> >     Response.Write "You got it!"
> > End If
> >
> > And CanExecute() would test somehow the permissions for the user ID on
> > the sp name passed as argument.
> >
> >