Board index » delphi » MS-SQL users/groups stored procedures for authentication

MS-SQL users/groups stored procedures for authentication

Anyone knows how to use MS-SQL's stored procedures to check on a
user's rights and group info from within Delphi? I would like to
authenticate the user using MS-SQL's user list.

Thanks
Andrew

 

Re:MS-SQL users/groups stored procedures for authentication


Hi Andrew

There are a few stored procedure that you can use to determine user
rights

sp_helprotect -> unusable, dreadfully slow, but it works on all objects
sp_table_privileges

If I want to get the permissions for a particular table, I use a
modification of sp_table_privileges that determines the permissions for
an object (or "%" for all user tables) and specifies whether or not they
CanInsert, CanDelete...

CREATE PROCEDURE sp_user_table_privileges (
                        @table_name             varchar(90),
                        @table_owner            varchar(90) = null,
                        @table_qualifier        varchar(32) = null)
as

if @table_qualifier is not null
begin
  if db_name() <> @table_qualifier
  begin  /* If qualifier doesn't match current database */
    raiserror 20001 'Table qualifier must be name of current database'
    return
  end
end

if @table_name is null
  select @table_name = '%'
if @table_owner is null /* If no owner supplied, force wildcard */
  select @table_owner = '%'

select
  convert(varchar(32),db_name()) TABLE_QUALIFIER,
  convert(varchar(32),user_name(o.uid)) TABLE_OWNER,
  convert(varchar(32),object_name(o.id)) TABLE_NAME,
  convert(bit, (Select 1
                From sysprotects p, sysusers u
                Where p.id = o.id and p.action = 193 and
                  p.protecttype <> 206 and ((p.uid = u.uid and
                  u.uid <> u.gid) or (p.uid = u.gid and u.uid<>u.gid))
                  and u.uid = User_Id() and not exists
                    (Select * From sysprotects p1
                     Where p1.protecttype = 206 and p1.action = 193
                     and p1.id = p.id and p1.uid = p.uid))) CanSelect,
  convert(bit, (Select 1
                From sysprotects p, sysusers u
                Where p.id = o.id and p.action = 195 and
                  p.protecttype <> 206 and ((p.uid = u.uid and
                  u.uid <> u.gid) or (p.uid = u.gid and u.uid <> u.gid))
                  and u.uid = User_Id() and not exists
                    (Select * from sysprotects p1
                     Where p1.protecttype = 206 and p1.action = 195
                     and p1.id = p.id and p1.uid = p.uid))) CanInsert,
  convert(bit, (Select 1
                From sysprotects p, sysusers u
                Where p.id = o.id and p.action = 196 and
                  p.protecttype <> 206 and ((p.uid = u.uid and
                  u.uid <> u.gid) or (p.uid = u.gid and u.uid <> u.gid))
                  and u.uid = User_Id() and not exists
                    (Select * from sysprotects p1
                     Where p1.protecttype = 206 and p1.action = 196
                     and p1.id = p.id and p1.uid = p.uid))) CanDelete,
 convert(bit, (Select 1
               From sysprotects p, sysusers u
               Where p.id = o.id and p.action = 197 and
                 p.protecttype <> 206 and ((p.uid = u.uid and
                 u.uid <> u.gid) or (p.uid = u.gid and u.uid <> u.gid))
                 and u.uid = User_Id() and not exists
                   (Select * from sysprotects p1
                    Where p1.protecttype = 206 and p1.action = 197
                    and p1.id = p.id and p1.uid = p.uid))) CanUpdate
from sysobjects o
where
  object_name(o.id) like @table_name
  and user_name(o.uid) like @table_owner
  and o.type = "U" /* User Tables Only */
GO

GRANT  EXECUTE  ON dbo.sp_user_table_privileges  TO public
GO

To determine the users group information you can use

select s.suid, s.uid, s.gid, s.name, g.name groupname
from sysusers s, sysusers g
where s.uid = User_Id() and s.gid = g.uid

HTH
Regards
Clint.

Quote
Andrew Diabo wrote:

> Anyone knows how to use MS-SQL's stored procedures to check on a
> user's rights and group info from within Delphi? I would like to
> authenticate the user using MS-SQL's user list.

> Thanks
> Andrew

Other Threads