locked
Database Permissions RRS feed

  • Question

  • Hello All,

    I have a two server MOSS 2007 SP2 Farm; version 12.0.0.6545 (Aug 2010 Cumulative). The OS is Windows Server 2003 R2 SP2 64-bit Enterprise. I am also running Forefront for SharePoint.

    The Farm is deployed in a least priviledged configuration using individual service accounts for just about everything. I also have two Shared Service Providers.

    Every morning around 1:59 AM I am receiving errors like the one below (I actually receive two identical ones around the same time which I'm pretty sure has to do with having two SSPs in the environment.)

    In this paticular case the stored procedure is 'proc_SecAddUser'. What I've had to do to resolve similar problems is to add the Server1$, Server2$ accounts to the SharePoint_Content and SharePoint_Admin databases, add those accounts to the WSS_Content_Application_Pools roles for each database and then apply Grant rights to that role for the given SP.

    This clears up the problem for the given stored procedure in the error but the next day, at the same time, I'll get more errors telling me there are more stored procedures that cannot be executed.

    Rather than doing this every day until the errors stop, I'd like to determine what process/job is responsible for the error and why there are any processes accessing the database as 'SYSTEM' in the first place.

    I've increased logging levels and have reviewed Timer jobs around that time and still cannot determine what process is running as 'SYSTEM'.

    The error just below is from the Event Log. 12 hive logs are available upon request. Thanks in advance for any assistance.

    Event Type: Error
    Event Source: Windows SharePoint Services 3
    Event Category: Database
    Event ID: 5214
    Date:  3/30/2011
    Time:  1:59:21 AM
    User:  N/A
    Computer: myServerName
    Description:
    Insufficient SQL database permissions for user 'SYSTEM' in database 'SharePoint_AdminContent_<id> on SQL Server instance '<myServerName>\<myInstanceName'. Additional error information from SQL Server is included below.

    The EXECUTE permission was denied on the object 'proc_SecAddUser', database 'SharePoint_AdminContent_<id>', schema 'dbo'.

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp

    Wednesday, March 30, 2011 1:50 PM

All replies

  • Hi Jason,

    Please refer 

    http://technet.microsoft.com/en-us/library/cc561019(office.12).aspx

     


    Warm Regards, Pratik Vyas | SharePoint Consultant | http://sharepointpratik.blogspot.com/
    Wednesday, March 30, 2011 1:58 PM
  • Hello Pratik,

    I have looked at that article in the past. Every Web application is running its own app pool and each app pool has its own identity. When MOSS creates the Web application it is supposed to apply the appropriate database permissions to the app pool identities. I have confirmed that all app pool identities have been added to the WSS_Content_Application_Pools roles for the SharePoint_Admin and Content databases.

    So I still need to determine what process is running as 'SYSTEM' and why I am having to manually adjust permissions for stored procedures. Maybe the process running as 'SYSTEM' should be running under the database access account? It seems logicial because it's pretty much the only account related to MOSS, that by default, has permissions to execute these stored procedures. If the process was application pool specific and the app pool identity wasn't added to the WSS_Content_Application_Pools role, even if I did add the missing identity to the role I would still have the problem of manually granting Execute to all these stored procedures.

    Wednesday, March 30, 2011 2:32 PM
  • Hello All,

    Any thoughts?

    Monday, April 4, 2011 2:52 PM
  • This might help. If you havent found this yet, it explains the "SYSTEM" account. While not an answer, it may give some closure about what the account actually does, and possibly give some hints as to what processes it is running.

     

    http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/2986a020-b1bd-46a9-8f97-dbd439664f6a

     

    Edit: I would also pop open your SQL logs and your ULS logs and do a search for the user name and see what type of output you are getting. There is a chance it could tell you what services are failing(and why, in a little more detail). The little bit ive looked into this, it may be a case where you have to grant DB authority to the SYSTEM account. That way, any jobs running under the SYSTEM account will have access to the resources they need.

    As I am not totaly familiar with your environment, im not sure if granting those permissions would be a complete answer. Hopefully your logs can tell you more.

    • Marked as answer by Leoyi Sun Friday, April 8, 2011 9:14 AM
    • Unmarked as answer by Jason Coen Monday, April 11, 2011 2:07 PM
    Wednesday, April 6, 2011 2:29 PM
  • Following up:

    I granted DBOWNER on SharePoint_Admin and SharePoint_Content to the <SERVERNAME>$ accounts (the two servers MOSS is installed on). This resolved the access denied errors but really, if anyone can tell me why the system accounts require this level of access it would be greatly appreciated.

    Please see original post for environment information. I am happy to provie any additional details that may be required.

    Thanks everyone for your responses thus far.

    Monday, April 11, 2011 2:11 PM
  • You said you used service accounts for "just about everything". It seems to me that the service accounts on the other end of that statement may be responsible. It looks as if your system may still be using the SYSTEM account as one of the service accounts.

    Here is a thread that describes ways to find out what service accounts everything is using:

    http://social.technet.microsoft.com/Forums/en-US/sharepointadmin/thread/90242970-3883-4782-aabb-3d3a21f14c5f/

    Hope this helps!

    Wednesday, April 13, 2011 2:38 PM