Script to Add a user as a ReadOnly user on All DBs

Once you have created a Login and mapped it to all DB, you might need to give it some type of access on all DBs. Here is a script to give Read-only access to the user in all DBs:


select 'use ['+name +']'+ char(13) +
' EXEC sp_addrolemember ''db_datareader'', ''ReadOnlyUserName''
' from master..sysdatabases

Reducing Initial TempDB Size

If the initial TempDB size is set to a higher value than what you want to set then this might not let  you do. This might cause problems on SQL Server 2005 and earlier versions. It would throw the following error:

"MODIFY FILE failed. Specified size is less than current size"

However this issue has been fixed in later versions as per update from Microsoft:

http://connect.microsoft.com/SQLServer/feedback/details/285172/ssms-cannot-modify-tempdb-initial-size-appropriately

Workaround in SQL Server 2005:

1.) Stop SQL Server
2.) Start SQL Server in Minimal Configuration Mode:

sqlservr -f -sDATAC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe -f -sInstanceName

3.) Connect to the server from SQLCMD utility as only single connection can be made to the server.

sqlcmd -s InstanceName

4.) Execute the alter TempDB statements on SQLCMD utility

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev,
SIZE = 1024 MB)

5.) Restart SQL Server in Normal Mode.

Model Logs and Data files in use

Sample Error from Events log when you try to start an SQL Server Instance:

FCB::Open: Operating system error 32(The process cannot access the file because it is being used by another process.) occurred while creating or opening file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.3\MSSQL\DATA\modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.

Possible reason is that there is another instance installed on the machine which is pointing to the same Model DB. The First instance can be started only after this instance is stopped.

Instance Name Not Foumd

There might be several reasons when you try to connect to an SQL Server Instance but it fails. The Server is reachable but it might not be possible to connect to its instance.

Sample Error:


Cannot connect to ServerName.

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)


 In such cases, Look for the following:

Open the following ports and Programs:

1. Management Studio In Program -- At Client
2. Management Studio Out Program -- At Client
3. TCSP Port 1433 -- At Target Server
4. UDP Port 1434 -- At Target Server
5. sqlservr.exe IN -- At Target Server
6. Open the port TCP 8080 to make sure that Machine is able to connect to outside the network -- At Client