Thursday, March 15, 2007

Object model code and stsadm fail with "Access Denied"

This frustrated me when I was trying to use my utility pack on a server that I had local admin rights on. Every time the utility pack tried to do something in code that involved sharepoint (like SPSite site = new SPSite(path)) I go an access denied error, even though I was an admin of the site as well!
I then noticed that it's not just the utility pack that is not working. Backup would fail with similar errors, and stsadm would not install my solutions.Access Denied! Access Denied! Access Denied!

After some research I came to the conclusion that direct SQL permissions are required for the user who is running the sharepoint code. For web parts, event handlers and workflows this is usually the application pool user who is given permissions on the SQL databases directly when you create the web application. But if you want to run a console application or a windows application like mine, or if you want to do actions like backup or installations using stsadm, you still need to have those rights.

My theory got confirmed a couple of days ago in the msdn forums, in a post where someone asked about a similar problem and I replied with what I knew. Pat Miller from Microsoft replied to affirm my theory and said "If you run code in the context of the web site, the account that the web server is running has access to the SQL box. When the system needs access to SQL, it can revert to the process account (which has rights). However if you are running from a command line, there is no underlying account that can be reverted to. Instead, the account running the operation has to have access to the appropriate resources. ".

I asked him about least privileges needed by an account to run server code, but he couldn't give me specifics, so this is still open. I worked with my DBA and he concluded that "Datareader/datawriter provide the ability to access/update any table in the DB. It appears that the scripts also call on stored procs and this is why the db_owner rights were required.". He adds that we could try to trim down the permissions for the user and find the least privileges, but we both don't have the time to do that right now.

I wonder if anyone else knows what minimum set of rights are required to run code on the server (and stsadm of course).

1 comment:

Ram said...

With Windows 2008 machines, I got the same problem even when the a/c which is logged in is the local admin and has access to the SQL DB's.
When I ran the command prompt with elevated (run as admin) it worked without any issues