Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Restore Master Database for a new dba Expand / Collapse
Author
Message
Posted Tuesday, July 29, 2014 7:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 33, Visits: 527
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.
Post #1597275
Posted Tuesday, July 29, 2014 12:52 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
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.
Post #1597458
Posted Tuesday, July 29, 2014 1:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
Before you go blindly restoring the master database, what permissions were lost? Were they SQL Server permissions, SharePoint permissions?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1597466
Posted Tuesday, July 29, 2014 1:21 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 33, Visits: 527
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.
Post #1597469
Posted Tuesday, July 29, 2014 1:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 33, Visits: 527
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.
Post #1597476
Posted Tuesday, July 29, 2014 1:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1597479
Posted Tuesday, July 29, 2014 1:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 33, Visits: 527
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?
Post #1597481
Posted Tuesday, July 29, 2014 2:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1597488
Posted Wednesday, July 30, 2014 3:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:16 AM
Points: 6,473, Visits: 13,930
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"
Post #1597637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse