locked
how to find who has what permission to each list item by direct sql query to content database? RRS feed

  • Question

  • I am looking for a direct query to some tables in content database to find out who has what permission to each document in a doc lib?

    I would like to use the results to audit the permission settings.

    Thanks,

    Guangming

    Thursday, July 7, 2011 6:28 PM

Answers

  • It's generally considered bad mojo to query the content databases directly, and it's not supported either.

    You should use the SharePoint Object model here and possible a simple console application.  Is there a specific document library you are concerned about? all document libraries for a given site? or every document library on your farm? 

    there are also 3rd party tools out there that will help you get this kind of visibility into your sites. 


    The SharePoint Hillbilly
    Fewer Big Words... More Pretty Pictures... http://www.SharePointHillbilly.com
    • Marked as answer by Emir Liu Thursday, July 14, 2011 6:23 AM
    Friday, July 8, 2011 1:56 AM
  • Hi,

     

    According to your description, in my opinion, the WFE builds a transactional dynamic SQL query to add the user to the document library's "Contributor" role and to add or update the user's property information in the user information list. This query is sent to the SQL server using TDS. On the SQL server, the following actions occur:

    1.        The query begins a new SQL transaction.

    2.        The query attempts to add the user to the site collection's user information list using the stored procedure proc_SecAddUser.

    3.        The query checks if the user exists in the site collection's user information list.

    4.        If the user is not found in the site collection's user information list, then the query attempts to add the user's properties to the site collection's user information list using the stored procedure proc_AddListItem.

    5.        The query rolls back the SQL transaction if the previous procedures were not successful, or it commits the transaction if they were successful.

     

     

    Best Regards
    David Hu
    • Marked as answer by Emir Liu Thursday, July 14, 2011 6:23 AM
    Monday, July 11, 2011 1:27 AM

All replies

  • It's generally considered bad mojo to query the content databases directly, and it's not supported either.

    You should use the SharePoint Object model here and possible a simple console application.  Is there a specific document library you are concerned about? all document libraries for a given site? or every document library on your farm? 

    there are also 3rd party tools out there that will help you get this kind of visibility into your sites. 


    The SharePoint Hillbilly
    Fewer Big Words... More Pretty Pictures... http://www.SharePointHillbilly.com
    • Marked as answer by Emir Liu Thursday, July 14, 2011 6:23 AM
    Friday, July 8, 2011 1:56 AM
  • You can very easily get this information using custom code. 3rd party tool, Avepoint DocAve 5 Admnistrator module, can help too.

    Let me know if you need a code sample.


    Steve Thomas
    Sunday, July 10, 2011 2:06 AM
  • Hi,

     

    According to your description, in my opinion, the WFE builds a transactional dynamic SQL query to add the user to the document library's "Contributor" role and to add or update the user's property information in the user information list. This query is sent to the SQL server using TDS. On the SQL server, the following actions occur:

    1.        The query begins a new SQL transaction.

    2.        The query attempts to add the user to the site collection's user information list using the stored procedure proc_SecAddUser.

    3.        The query checks if the user exists in the site collection's user information list.

    4.        If the user is not found in the site collection's user information list, then the query attempts to add the user's properties to the site collection's user information list using the stored procedure proc_AddListItem.

    5.        The query rolls back the SQL transaction if the previous procedures were not successful, or it commits the transaction if they were successful.

     

     

    Best Regards
    David Hu
    • Marked as answer by Emir Liu Thursday, July 14, 2011 6:23 AM
    Monday, July 11, 2011 1:27 AM