Ever get the error “Access Denied” while trying to add/remove a user/group from a SharePoint Site Collection? If you are like me and realize that the user you are logged in with has Site Admin priveleges, you are probably scratching your head like I was. If you are like me, those scratches left a mark on your bald head too! Enter bug in SharePoint.
If you have a Site Collection marked as “readonly” and it happens to be the last Site in the SharePoint table Sites, it will basically disable you from editing EVERY user/group in SharePoint that is within the same Content Database.
So how could someone figure this out? A lot of SQL skills help, and I gots em…
To replicate this problem, do the following:
- Create a new Web Application and a new Content Database in Central Administration (WEB APP=http://test, Content Database=SP_TEST_CONTENT)
- Create as many site collections as you want:
- “/” root Blank
- “/sites/T1” Blank
- “/sites/T2” Blank
- Open up SQL Management Studio (SSMS) and run a query like this: SELECT s.id,s.BitFlags,w.FullUrl FROM SP_TEST_CONTENT..Sites s JOIN SP_TEST_CONTENT..Webs w ON s.Id = w.SiteId
- Take note to the one that is last, if it is “”, it is root. Now, go to a command line and do the following: “stsadm -o setsitelock -url http://test/<Full URL from above> -lock readonly”
- Go to any of the site collections that ARE NOT marked as read-only and try to add a user/group…ACCESS DENIED
So what is the deal? SQL skills take over… I opened up SQL Profiler and watched what was going on. The one line that got me was calling “proc_SecAddUserToSiteGroup”. Upon looking at this SPROC with “sp_helptext proc_SecAddUserToSiteGroup” I saw another call to “fn_CanUserEditMembershipOfGroup”. After doing the same thing on this call, found a line like the following:
SELECT @SiteFlags = BitFlags FROM Sites
All in all valid SQL statement, however, one big gaping hole…uh….WHERE CLAUSE!!!!!!!!
After this call, it looks for IF @SiteFlags & 131072 = 131072 RETURN 0. DOH…that pretty much mean ACCESS DENIED!!!!
Microsoft, please fix this bug…