July 29, 2014 at 7:17 am
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
July 29, 2014 at 12:52 pm
Is this cluster running with Always On? In that case, you would need to create all of the logins on both instances of SQL Server.
July 29, 2014 at 1:10 pm
Before you go blindly restoring the master database, what permissions were lost? Were they SQL Server permissions, SharePoint permissions?
July 29, 2014 at 1:21 pm
They were SQL login account permissions and were ad accounts and they were not documented. I agree that it seems like overkill but the SharePoint admin feels that we don't know what other permissions were "lost" so we should restore them all. And we don't use always on.
July 29, 2014 at 1:27 pm
Beyond restoring the master database, what ways are possible to restore the master from a backup and compare or apply the difference in permissions. We have located the accounts that seem to be causing problems.
July 29, 2014 at 1:30 pm
Lynn Pettis (7/29/2014)
Before you go blindly restoring the master database, what permissions were lost? Were they SQL Server permissions, SharePoint permissions?
I would restore the master database as an ordinary user database with a name like master_logins. Then I would look for the differences between the restored master database and active master database. Recreated the missing logins based on what you find and then fix the orphans, since you said they were SQL Logins.
July 29, 2014 at 1:42 pm
I have restored the master in this way to another server. How would I go about looking for differences? Do you have any suggested queries that could help me out?
July 29, 2014 at 2:06 pm
Nope. You start with sys.server_principals. You'll have to do some reading in BOL to identify any of the other tables you may need to look at. Unfortunately I don't have a lot of time right now to dig into it right now myself.
July 30, 2014 at 3:55 am
JeepHound (7/29/2014)
Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
When starting the instance in single user mode use the following to restrict connections to the instance.
sqlservr -c -m"SQLCMD"
You could also just turn the windows firewall on and block connections temporarily 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply