Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Location
    Winnipeg, Manitoba, Canada
    Posts
    2

    Unanswered: Need information on syspermissions table

    I have a MS Sqlserver 2000 database installed on my Windows NT4 PC.

    After I granted permissions to a user for a specific table my Java code
    (using JDBC - ODBC) was successfully able to retrieve the contents of the
    table. However, when I examined the contents of the "syspermissions" table
    using the following command :

    osql /U sa /P /d %1 /w 256 -Q "select grantee,grantor,actadd,actmod,
    (select substring(name,1,40)
    from sysobjects where sysobjects.id = syspermissions.id) 'objname'
    from syspermissions,sysobjects where
    (sysobjects.xtype = 'U' and sysobjects.id = syspermissions.id) order by objname"


    I did not see any difference in the entry for the table to which I granted
    the access.

    According to the documentation I have read the "syspermissions" table is the
    place where access rights information is stored.

    Am I missing something ? Where is the info on granted access rights stored
    if not in the "syspermissions" table ?

    Thanks for any help you can give.

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Post Re: Need information on syspermissions table


    Q1 Thanks for any help you can give.
    A1 GRANT and DENY statements populate sysprotects (which you have not queried). You may wish to consider using exec sp_helprotect instead?

    Example:

    Use Pubs
    Go

    exec sp_helprotect
    @name = 'Authors'
    Go

    exec sp_helprotect
    @name = 'Authors',
    @username = 'SomeOtherUser' ,
    @grantorname = NULL,
    @permissionarea = 's'
    Go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •