How To Rebuild/rescover The Master Database In Sql 2005
Aug 23, 2007
Hi, I'm testing my recovery on a sql server 2005 database. The server has been restored, which includes the OS and sql server 2005 installation (binaries, full tape restore). The tape restore didn't put back the master.mdf and other .mdf files (can't be backed up when open), so I need to rebuild the master and do a database recovery on it and the other supporting databases (yes, I have database backups). I have installed the sql 2005 setup CDs in a folder and run the following command to rebuild the master, but nothing happens, it pauses for a couple of minutes before it returns to the prompt, but it does not put and new master.mdf files in the folder and I can't start the service in single user mode. "start /wait setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=mypassword" Anybody have any procedures for a full sql server 2005 recovery or suggestions on this, please post.
I need to rebuild a corrupted/deleted master database in SQL Server 2005 SP2.
When I run setup.exe from the install media as directed by SQL Books Online, I get to the point where it says the instance is a later version and setup cannot continue. Rerunning the SP2 installer, the instances cannot be selected for upgrade (and it ignores the command line options for the original installer).
How to proceed?
Also, it would seem simpler to have a detach, copy and attach the system databases process, rather than run the installer. (I realize you can stop the instance, copy files, restart the instance; but that's not "high availability".)
Complete: SetPackageInstallStateAction at: 2007/9/23 7:33:43, returned true Running: DeterminePackageTransformsAction at: 2007/9/23 7:33:43 Complete: DeterminePackageTransformsAction at: 2007/9/23 7:33:43, returned true Running: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43 Complete: ValidateSetupPropertiesAction at: 2007/9/23 7:33:43, returned true Running: OpenPipeAction at: 2007/9/23 7:33:43 Complete: OpenPipeAction at: 2007/9/23 7:33:43, returned false Error: Action "OpenPipeAction" failed during execution. Running: CreatePipeAction at: 2007/9/23 7:33:43 Complete: CreatePipeAction at: 2007/9/23 7:33:43, returned true Running: RunRemoteSetupAction at: 2007/9/23 7:33:43 <Func Name='CProcessCtrl::GetInstallPath'> <EndFunc Name='CProcessCtrl::GetInstallPath' Return='0' GetLastError='0'> Error: 0x80070050 TaskScheduler::NewWorkItem for SQL Server Remote Setup Error: 0x80070005 TaskSchedulerWorkItem failed to save the task [SQL Server Remote Setup ] Complete: RunRemoteSetupAction at: 2007/9/23 7:33:43, returned false Error: Action "RunRemoteSetupAction" failed during execution. Error information reported during run: Attempting to determine log files for remote install. Connection to remote computer's scheduler service. Creating new workitem. Deleting existing work item and trying again... Starting remote setup onSQL1N2 Error: 80070005 Access is denied. Running: PopulateMutatorDbAction at: 2007/9/23 7:33:43 Complete: PopulateMutatorDbAction at: 2007/9/23 7:33:43, returned true Running: GenerateRequestsAction at: 2007/9/23 7:33:43 SQL_Engine = 3 SQL_Data_Files = -1 SQL_Replication = -1 SQL_FullText = -1 SQL_SharedTools = -1 SQL_BC_DEP = -1 Analysis_Server = -1 AnalysisDataFiles = -1 AnalysisSharedTools = -1 RS_Server = -1 RS_Web_Interface = -1 RS_SharedTools = -1 Notification_Services = -1 NS_Engine = -1 NS_Client = -1 SQL_DTS = -1 Client_Components = -1 Connectivity = -1 SQL_Tools90 = -1 SQL_WarehouseDevWorkbench = -1 SDK = -1 SQLXML = -1 Tools_Legacy = -1 TOOLS_BC_DEP = -1 SQL_SSMSEE = -1 SQL_Documentation = -1 SQL_BooksOnline = -1 SQL_DatabaseSamples = -1 SQL_AdventureWorksSamples = -1 SQL_AdventureWorksDWSamples = -1 SQL_AdventureWorksASSamples = -1 SQL_Samples = -1 Complete: GenerateRequestsAction at: 2007/9/23 7:33:44, returned true Running: CreateProgressWindowAction at: 2007/9/23 7:33:44 Complete: CreateProgressWindowAction at: 2007/9/23 7:33:44, returned false Error: Action "CreateProgressWindowAction" failed during execution. Running: ScheduleActionAction at: 2007/9/23 7:33:44 Complete: ScheduleActionAction at: 2007/9/23 7:33:45, returned true Skipped: InstallASAction.11 Waiting for actions from remote setup(s) Breaking wait state and aborting package due to cancel code received: 1602 Remote setup(s) are ready Notify package action is determined: 1602 Error Code: 0x800700e9 (233) Windows Error Text: No process is on the other end of the pipe.
Source File Name: remotemessageliboverlappedpipelistener.cpp Compiler Timestamp: Sat Oct 7 09:43:54 2006 Function Name: sqls:verlappedPipeListener::writePipe Source Line Number: 294
Notification failed to send. ---- Context ----------------------------------------------- sqls::HostSetupPackageInstallerSynch::installAction
Removing machine from list of targets to sync. Skipped: Action "InstallASAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "as", will not be installed. Skipped: InstallASAction.18 Skipped: Action "InstallASAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "as", will not be installed. Skipped: InstallASAction.22 Skipped: Action "InstallASAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "as", will not be installed. Skipped: InstallASAction.9 Skipped: Action "InstallASAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "as", will not be installed. Skipped: InstallDTSAction Skipped: Action "InstallDTSAction" was not run. Information reported during analysis: All installs have been cancelled, so package: "dts", will not be installed. Skipped: InstallDTSAction.11 Skipped: Action "InstallDTSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.12 Skipped: Action "InstallDTSAction.12" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlncli", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.18 Skipped: Action "InstallDTSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.22 Skipped: Action "InstallDTSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "dts", will not be installed. Skipped: InstallDTSAction.9 Skipped: Action "InstallDTSAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "dts", will not be installed. Skipped: InstallNSAction Skipped: Action "InstallNSAction" was not run. Information reported during analysis: All installs have been cancelled, so package: "ns", will not be installed. Skipped: InstallNSAction.11 Skipped: Action "InstallNSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "ns", will not be installed. Skipped: InstallNSAction.12 Skipped: Action "InstallNSAction.12" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlncli", referred by package: "ns", will not be installed. Skipped: InstallNSAction.18 Skipped: Action "InstallNSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "ns", will not be installed. Skipped: InstallNSAction.22 Skipped: Action "InstallNSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "ns", will not be installed. Skipped: InstallNSAction.9 Skipped: Action "InstallNSAction.9" was not run. Information reported during analysis: All installs have been cancelled, so package: "msxml6", referred by package: "ns", will not be installed. Skipped: InstallRSAction.11 Skipped: Action "InstallRSAction.11" was not run. Information reported during analysis: All installs have been cancelled, so package: "sqlsupport", referred by package: "rs", will not be installed. Skipped: InstallRSAction.18 Skipped: Action "InstallRSAction.18" was not run. Information reported during analysis: All installs have been cancelled, so package: "owc11", referred by package: "rs", will not be installed. Skipped: InstallRSAction.22 Skipped: Action "InstallRSAction.22" was not run. Information reported during analysis: All installs have been cancelled, so package: "bcRedist", referred by package: "rs", will not be installed. Skipped: InstallSqlAction Clustered feature detected: SQL_Engine Clustered feature detected: SQL_FullText Loaded DLL:C:Program FilesMicrosoft SQL Server90Setup Bootstrapsqlsval.dll Version:2005.90.3042.0 Windows Error Text: User cancelled installation.
Source File Name: sqlchainingsqlchainingactions.cpp Compiler Timestamp: Thu Nov 16 20:32:00 2006 Function Name: sqls::ReportChainingResults:erform Source Line Number: 3667
---- Context ----------------------------------------------- sqls::RunRemoteSetupAction::waitForRemoteSetupComplete king package: "patchRS2000" as failed due to cancel code received from cancel source: 1602 sqls Delay load of action "UploadDrWatsonLogAction" returned nothing. No action will occur as a result. Message pump returning: 1602
In SQL Server 2005 Express, how do I rebuild the master database?
I am receiving the following error messages.
The log scan number (888:136:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
AND
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
I tried going through Add and Remove Programs. It did not recognize anything wrong. It did nothing.
I tried 'setup' from the install media. It said to go to 'Add and Remove Programs.'
In SQL Server 2005 Express, how do I rebuild the master database?
If one is regularly taking backups of system databases, when does it become necessary to rebuild the master database. I am looking for a situation where rebuilding the master is preferred to restoring it from backup.
I'm trying to rebuild my master database for sql server 2000. The process of rebuilding stared fine. But it is almost 4 hours since it got started. Performing it on a test system. Got doubtful and started the same on another test system. Issue is same and it is almost 2 hours. The Db size is less than 100 MB in both cases. IS IT NORMAL? I've tried the same for SQL SERVER 2005 and it got finished in couple of minutes. Please advise.
I am performing a Rebiuld of the Master database using the REBUILDM utility on a SQL 2000 SP3 database which is clustered. The utility starts off correclty, copying the correct MDF and LDF files to the hard drive, and the configuration bar goes across the screen 4 times, before giving the following error "Rebuild master failed with error -1".
I can't find anything on microsoft. I followed KB-298568 to perform this process, but the SQL Service will now not start as it cannot find the master, as it did not get rebuilt properly.
I have included screen shots of the error, in the zipped up word doc.
I've been in support for over ten years, but have had minimal exposure to SQL. I have a client who is running SQL 2005 (as part of SBS Premium), who experienced a server crash due to the database "administrator" filling up the C: drive with manual SQL backups.
Anyway, if I go into SQL Management Studio and try to pull up properties of any database I get a "cannot show requested dialog" error. The only other issue that I've found is that a maintenance plan doesn't quite work correctly -- It will backup their database, but not delete old copies off the hard drive. Otherwise, SQL seems to be running okay.
I have come to the conclusion that I probably need to rebuild the master database. I have tried restoring the master database from a version prior to the crash, but I still have the same issue. I believe the rebuild should be fairly easy, but just don't have a full understanding of what it will affect. They have one custom application that uses a single SQL database and very little security customization (the custom application uses one Windows AD account to access the database).
Basically, I'm wondering:
1. Can someone summarize what is contained in the master database 2. I assume that I'm looking at downtime on their custom application/database - Would this be correct? 3. Will this affect their database at all? 4. How long does the database rebuild process take? 5. I understand that in SQL 2005, the rebuild process it part of the setup on the CD. If the problem is with SQL application files themselves, will this process restore any corrupt program files?
I'm using SQL SERVER 2000 and in the process of establishing recoveryguide.Tried to rebuild master using rebuildm.exe but the "Configuring Server"never ending. Then I tried to copy original databases from SQLServerInstallation CD to hard disk as the "Browse for folder", but gave thesame problem. Need advice what need to be done.
Looks like I'm screwed. Next problem - can you rebuild the master.mdf without the mastlog.ldf and how? Also, on an active/active SQL server setup, how would you re-install Node 1?
I tried to test Rebuildm.exe on my local server 1. Stoped Sql 2. Renamed master.mdf to master.mdf_old 3. Tried to start Sql (Does start of couse) 4. Run Rebuildm.exe ... and "Gonfigurating Sql server" message goes forever (I was waiting 1 1/2 hrs ... progress bar was running )
How I can rebuild master db to change the Code Page of the current SQL7 , to convert my SQL7 from code page 850 to 1252 ? I have huge data in the old code page dbs , Does rebuild the master db mean reinsert the data again ?
Login failed for user 'OFFICIALPROJECTAdministrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
SQL Server 2005 version: 2153 I created a maintplan for system and user databases includes rebuild index, maint cleanup tasks.
Job is failing for user databases It includes rebuild index task( online index enabled) and maintenance cleanup task, scheduled at every sunday 1 AM.
I receive following errors:
In eventvwr log
sql server scheduled job 'DBMP_RebuildIndex_User' status: failed-Invoked on 2007-12-02 -1:00 Message: The job failed. The job was invoked by schedule 8 ('DBMP_RebuildIndex_User-Schedule).The last step to run was step1 ('DBMP_RebuildIndex_User')[/red]
In log report:
Failed:(-1073548784) Excuting the query "ALTER INDEX [XPKact_log] ON [dbo].[act log] REBUILD WITH (PAD_INDEX=OFF, STATISTICS_NORECOMPUTE=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON,SORT_IN_TEMPDB=OFF,ONLINE=ON) "failed with the following error "Online index operation cannot be performed for index 'XPKact_log' because the index contains column 'action_desc' of data type text, ntext.image.varchar(max),varbinary(max) or xml. For non clusterd index the column could be an include column of the index. for clusterd index it could be any column of the table .Incase of drop_existing the cloumn could be part of new or old index. The operation must be performed offline". Possible failure reasons : Problems with the querey .'" Resultset" property not set correctly, parameters not set correctly, or connection not established correctly.
Please anyone help me on this? I really appriciate
I need to rebuild master on an instance of SQL 2005. The instance was installed incorrectly and would like to rebuild the instance with the proper collation. I know the command to rebuild (below), but I'm not sure what to put in for the collation. The collation needs to be:
Dictionary order, case-insensitive, uppercase preference for use with the 1252 Character set
Here is the command: setup.exe /qn INSTANCENAME=QNXT REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=******* SQLCOLLATION=<NewSystemCollation>
What do i place in the <NewSystemCollation> in order to get the collation above installed?
Is there a "generic" script I may run to delete the tables and stored procedures to a SqlServer 2000 database.I would really like to delete every table and stored procedure I have created as quickly and efficiently as possible.
I have never done this in a DR scenario before. What I used to do in SQL Server 2000 was apply the logins to a new installation of SQL 2000 then recover the application databases. I'm using 2005 now and I'm in a different mode where I would like to be able to recover the system (master, model, msdb) to a different server with the same SQL Server 2005 build ---- 9.0.3054. I'm following the procedures from Microsoft where they state to place the instance in single user mode first then invoke SQLCMD then perform the restore. Sounds simple enough. C:> SQLCMD > RESTORE DATABASE MASTER from DISK = 'Z:MINIDRmaster_backup_200804200315.bak'
The console is coming back with a message that the backup set holds a backup other than the existing master database. So I suppose that this cannot be done and is only intended for recovery on the same instance.
On our particular database server, we run the Rebuild Index Task (Using classic Maintenance Plan Designer) every night. Running the script below, I saw that about 77 tables had an avg_fragmentation_in_percentage between 80% and 99% !!
SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, indexstats.index_type_desc AS IndexType, indexstats.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats INNER JOIN sys.indexes ind ON ind.object_id = indexstats.object_id AND ind.index_id = indexstats.index_id WHERE indexstats.avg_fragmentation_in_percent > 30--You can specify the percent as you want ORDER BY indexstats.avg_fragmentation_in_percent DESC
I dont understand why these tables are highly fragmented after a daily index rebuild! Unless the users are doing heavy inserts/updates/deletes during the day.
I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?
We have 3 maintenance jobs configured in this particular DB instance:
Daily backup of system database - SubPlan1 (Check Database Integrity Task --> Rebuild Index Task-->Backup Database Task)Daily backup of user databases - Five subplans for each task : (Check DB integrity --> Rebuild Index -->Backup User Database, Backup Log -->Cleanup History)Weekly maintenance - SubPlan1 (Check Database integrity job (system+user DB) + rebuild index job (system+user DB) )
PROBLEM: I just noticed that the User DB Rebuild Index task has been running since the 03/04 and the Weekly maintenance plan - subplan1 since the 12/04.
Which job is "safe" to stop without impacting the database?
I have a requirement to only rebuild the Clustered Indexes in the table ignoring the non clustered indexes as those are taken care of by the Clustered indexes.
In order to do that, I have taken the records based on the fragmentation %.
But unable to come up with a logic to only consider rebuilding the clustered indexes in the table.
I had to to relocate the database log file and I issued an Alter database command but by mistake I put a space in the file name as below. The space is at the beginning file name. Now I am unable get the database loaded to SQL Server. The database has 2 replications configured, so deleting and re-attaching the database means the replication needs to be re-configured. Is there an alternative way to issue a command to update the database FILENAME ? Not sure if this can be edited in master database (sys files).
ALTER DATABASE [User_DB] MODIFY FILE (NAME = User_DB_log, FILENAME = 'I:SQLLogs User_DB_log.ldf') GO
got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'. An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360 i have this in my web.config file <connectionStrings> <add name="ConnectionStringTest" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False" providerName="System.Data.SqlClient" /> </connectionStrings> <appSettings /> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <compilation debug="true" /> <identity impersonate="true"/> and my asp connection string is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>" SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource> if i set user instance to true i get a user permission error. it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1 CREATE DATABASE permission denied in database 'master'. Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.
whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.
I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"
I created another windows user "TestUser" (Limited account) in the same physical PC.
I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']
At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.
I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.
SO HERE ARE MY QUESTIONS :
1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?
2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)
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.
I've tried starting SQL from the command line in single user mode (-m). When I try to open Management Studio I receive an error that it can't connect. Can you only restore master from a command line in SQL 2005? Or can I do it from a GUI?
