I’m glad I keep good notes. Once upon a time my network administrator informed me that keeping the content database .mdf and .ldf files in the same place was bad practice and had me move the .ldf files to another partition. This I did. Then recently, after creating three new content databases and sites, I remembered that I needed to do this and so I detached the database, moved the ldf files and then reattached. But suddenly my sites wouldn’t come up and in the error log I had the following:
A runtime exception was detected. Details follow.
Message: Cannot open database “site_Content” requested by the login. The login failed.
Login failed for user ‘domain\shareadmin’.
After a panicked 30 minutes of searching online, I realized I probably had a script somewhere that I had run to do the big ldf migration months ago. I found that I had in fact created a script to create content databases (and then promptly forgot about it and went back to creating content databases through the SharePoint Administrator.) I reran the portion of the script that granted access and my sites came back. Phew!
use site_content
DECLARE @sqladmin nvarchar(128)
DECLARE @shareadmin nvarchar(128)
SET @sqladmin = N’DOMAIN\sqladmin’
SET @shareadmin = N’DOMAIN\shareadmin’
EXEC sp_grantlogin @shareadmin;
EXEC sp_grantlogin @sqladmin;
EXEC sp_changedbowner @sqladmin;
IF NOT EXISTS (SELECT * FROM sysusers WHERE name=@shareadmin) EXEC sp_grantdbaccess @shareadmin;
EXEC sp_addrolemember ‘db_owner’, @shareadmin;
sharepointdiva
