August 31, 2008 at 11:53 pm
Hi Experts,
One of our production servers got crashed and recovered the server by attaching the files.Now the problem is that i want to get all the users and their respective privileages. How can i achieve that??
Thanks in Advance..
September 1, 2008 at 12:09 am
Do you have the master database or a backup of the master database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2008 at 1:11 am
Ya i have one..
September 1, 2008 at 1:14 am
The latest backup is one took last friday.
September 1, 2008 at 1:18 am
Have a look in books online for the procedure to restore the master database, and restore that backup of master. All of your logins and passwords are in there.
It's not the same as restoring a user database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2008 at 1:33 am
Thanks a lot
September 1, 2008 at 2:03 am
Hi,
When i tried to start the sql server in single user mode am getting the error
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr.exe -c -m AST0
26
2008-09-01 13:35:26.83 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:26.83 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:27.15 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:27.15 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:27.46 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:27.46 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:27.77 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:27.77 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:28.10 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:28.10 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:28.41 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:28.41 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:28.72 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:28.72 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:29.07 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:29.07 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:29.38 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:29.38 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
2008-09-01 13:35:29.69 Server Error: 17058, Severity: 16, State: 1.
2008-09-01 13:35:29.69 Server initerrlog: Could not open error log file 'C:
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. Operating syste
m error = 32(The process cannot access the file because it is being used by anot
her process.).
September 1, 2008 at 3:02 am
Have you shut the service down? Also make sure that nothing's using the error log file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2008 at 3:48 am
How long will it take to start in single user mode and how to come out of single user mode??
September 1, 2008 at 4:10 am
Not long, but it's not something you should be doing if users are accessing the DB.
Stop the service.
Start SQL in single user mode (takes probably as long as it takes to start the service)
Connect to SQL and restore the master database. Note, SQL's in single user mode. If someone else gets a connection before you, then that person is the single user. ie, make sure you do this when no one else will be trying to connect to the DB
Stop SQL (Ctrl-C in the cmd window that's running sql)
Restart the service.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2008 at 4:19 am
Hi ,
Given the command sqlservr -c -m before 2 hours and its still showing some messages like running some procedure and all.Why sql taking so long ???
Is there any other way to get all the logins from a crashed server???
September 1, 2008 at 4:32 am
Given the command sqlservr -c -m at its taking so long displaying messages like executing procedures.What to do am i going through the right way??
stopped services.
run sqlservr -c -m
Can i restore the master now using SSMS??? or any other way to do so??
Is there any other way to get all the logins and user information from the crashed server???
September 1, 2008 at 4:35 am
What's the messages that it's printed?
Can you connect to it (from sqlcmd running in another command session)?
Is there any other way to get all the logins and user information from the crashed server???
Only if you've previously scripted out the logins. The information on database users is in each individual user database (which you've recovered). It's the information on logins, passwords, server-level permissions and that kind of info that's in master, along with the details of the various user databases, linked servers, server-level triggers, certificates and a few other things.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2008 at 4:52 am
Command prompt showing
2008-09-01 14:38:26.40 spid4 Recovery complete.
2008-09-01 14:38:26.41 spid4 SQL global counter collection task is created.
2008-09-01 15:15:36.50 spid51 Using 'xpstar.dll' version '2000.80.760' to exe
cute extended stored procedure 'sp_MSgetversion'.
2008-09-01 15:44:23.31 spid51 Error: 15457, Severity: 0, State: 1
2008-09-01 15:44:23.31 spid51 Configuration option 'show advanced options' ch
anged from 1 to 1. Run the RECONFIGURE statement to install..
2008-09-01 15:44:23.45 spid51 Using 'xplog70.dll' version '2000.80.760' to ex
ecute extended stored procedure 'xp_msver'.
When i tried to restore the master backup the message is the versions are different cannot restore...
September 1, 2008 at 5:02 am
That's ready for connection, from the point that this message appeared.
2008-09-01 14:38:26.40 spid4 Recovery complete.
What version was the old server? (version and service pack)
What version is the new server? (version and service pack)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply