World Search

Tuesday, December 31, 2013

Create user for all existing databases

USE master;

DECLARE
    DBNames CURSOR
FOR
    SELECT
        NAME
    FROM sysdatabases

OPEN DBNames

DECLARE @Name varchar(50)

FETCH NEXT FROM DBNames
INTO @Name

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    Declare @SQL varchar(255)
    Print 'Processing: ' + @Name
    BEGIN TRY
       Select @SQL = ' USE ' + @Name + ' CREATE USER [ldbamonitor] FOR LOGIN ["LOGINNAME HERE"] EXEC sp_addrolemember N''db_datareader'', N''ldbamonitor'''
       EXEC(@SQL)
    END TRY
    BEGIN CATCH
        select ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    FETCH NEXT FROM DBNames    INTO @Name
END

CLOSE DBNames
DEALLOCATE DBNames

Monday, December 23, 2013

Database status is shutdown, normal mode



SQL Database stats Shutdown,Normal


If you got your "database status is shutdown, normal mode".
Run the below query for reset the database status to Normal mode.

ALTER DATABASE dbname SET AUTO_CLOSE OFF


Now the database should be Normal mode.

Monday, December 16, 2013

Cannot connect to WMI provider.You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 servers with SQL Server Configuration Manager.

Step 1:- Verify which version of sql server 2005/2008
Step 2:- Go to Shared folder on C drive "C:\Program Files\Microsoft SQL Server\90\Shared"
Step 3:- Verify the following file present in the folder
sqlmgmproviderxpsp2up.mof

Step 4:- If missing copy from other instance folder
Step 5:- Register as shown below,

C:\>mofcomp "C:\Program Files\Microsoft SQL Server\90\Shared
\sqlmgmproviderxpsp2up.mof"


SOLVED Problem.


Check the network address name and that the ports for the local and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)

 Change the SQL Server from local service to Service Sccount.

http://www.youtube.com/watch?v=9v0OW2WfoIo