Saving Changes Is Not Permitted On SQL Server 2008 Management Studio. How To Solve?

Saving changes is not permitted occurs when doing alter table (table structure is changed):

1. Change data type on existing columns
2. Or change allow nulls on existing columns

 
To allow you to save changes after you alter table, do disable prevent changes:
 

1. Open Microsoft SQL Server Management Studio 2008
2. Click Tools menu options, then click Options 
3. Select Designers 
4. Uncheck "prevent saving changes that require table re-creation" option
5. Click OK
6. Try to alter your table
7. Your changes will performed as desired  

Error while installing SQL Server 2005 – Native Client cannot be found

Recently while installing SQL Server 2005 Standard Edition on a “fresh” server I found that crazy thing just wouldn’t install!

I was getting the following error:

“An installation package for the product Microsoft SQL Server Native Client cannot be found. Try the installation again using a valid copy of the installation package ‘sqlncli.msi'”

Most cryptic… Considering my installation package is the disc given to me by Microsoft and I’m positive that the sqlncli.msi file is right there.

However, the actual issue appears to be as simple as an inability to upgrade an old Native Client edition.

To resolve, use the Add / Remove Programs panel to uninstall an existing SQL Server Native Client installation and you should be good to roll.


Note that this appears to be common for other editions of SQL Server 2005 too.

SQL SERVER – Enable Login – Disable Login using ALTER LOGIC – Change name of the ‘SA’

In SQL SERVER 2005, all the login (including ‘sa’ ) can be enabled and disabled using ALTER LOGIN command.

To disable ‘sa’ login:
ALTER LOGIN sa DISABLE
GO
To enable ‘sa’ login:
ALTER LOGIN sa ENABLE
GO
Also for additional security (prevent educated guess from hackers) the name of the ‘sa’ account can be changed.
ALTER LOGIN [sa] WITH NAME = [AdminUser]
GO

SQL SERVER – 2005 Query Analyzer – Microsoft SQL SERVER Management Studio

Following may be very simple to some and helpful to other type of question. I have seen this in my server log as well as this has been always first question in my Developer Team.

Where is SQL SERVER 2005 Query Analyzer?
SQL SERVER 2005 has combined Query Analyzer and Enterprise Manager into one Microsoft SQL SERVER Management Studio (MSSMS). To see the familiour Query Analyzer Window follow the image below.

SQL SERVER – Fix : Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection

Some errors never got old. I have seen many new DBA or Developers struggling with this errors.

Error: 18452 Login failed for user ‘(null)’. The user is not associated with a trusted SQL Server connection.

Fix/Solution/Workaround:
Change the Authentication Mode of the SQL server from “Windows Authentication Mode (Windows Authentication)”
to “Mixed Mode (Windows Authentication and SQL Server Authentication)”.

Run following script in SQL Analyzer to change the authentication

LOGIN sa ENABLE
GOALTER LOGIN sa WITH PASSWORD = ''GO

OR

In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties. On the General page, you may have to create and confirm a password for the sa login. On the Status page, in the Login section, click Enabled, and then click OK.

Create exceptions in Windows Firewall

These steps apply to the version of Windows Firewall that is included in Windows XP Service Pack 2 (SP2) and in Windows Server 2003. If you are using a different firewall, see your firewall documentation for more information. 

If you are running a firewall on the computer that is running SQL Server 2005, external connections to SQL Server 2005 are blocked unless SQL Server 2005 and the SQL Server Browser service can communicate through the firewall. You must create an exception for each instance of SQL Server 2005 that you want to accept remote connections and an exception for the SQL Server Browser service.

SQL Server 2005 uses an instance ID as part of the path when you install its program files. To create an exception for each instance of SQL Server, you have to identify the correct instance ID. To obtain an instance ID, follow these steps:

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In SQL Server Configuration Manager, click the SQL Server Browser service in the right pane, right-click the instance name in the main window, and then click Properties.
3. On the SQL Server Browser Properties page, click the Advanced tab, locate the instance ID in the property list, and then click OK.

To open Windows Firewall, click Start, click Run, type firewall.cpl, and then click OK.

Create an exception for SQL Server 2005 in Windows Firewall

To create an exception for SQL Server 2005 in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe, click Open, and then click OK. 
   
   Note The path might be different, depending on where SQL Server 2005 is installed. MSSQL.1 is a placeholder for the instance ID that you obtained in step 3 of the previous procedure.
4. Repeat steps 1 through 3 for each instance of SQL Server 2005 that needs an exception.

Create an exception for the SQL Server Browser service in Windows Firewall

To create an exception for the SQL Server Browser service in Windows Firewall, follow these steps:

1. In Windows Firewall, click the Exceptions tab, and then click Add Program.
2. In the Add a Program window, click Browse.
3. Click the C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe executable program, click Open, and then click OK. 
   
   Note The path might be different, depending on where SQL Server 2005 is installed.

Enable the SQL Server Browser service

If you are running SQL Server 2005 by using an instance name and you are not using a specific TCP/IP port number in your connection string, you have to enable the SQL Server Browser service to allow for remote connections. For example, SQL Server 2005 Express is installed with a default instance name of Computer Name\SQLEXPRESS. You only have to enable the SQL Server Browser service one time, regardless of how many instances of SQL Server 2005 you are running. To enable the SQL Server Browser service, follow these steps.

Important These steps may increase your security risk. These steps may also make your computer or your network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to, or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you choose to implement this process, take any appropriate additional steps to help protect your system. We recommend that you use this process only if you really require this process.

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, click SQL Server Browser, click Automatic forStartup type, and then click Apply. 
   
   Note When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.
4. Click Start, and then click OK.

Note When you run the SQL Server Browser service on a computer, the computer displays the instance names and the connection information for each instance of SQL Server that is running on the computer. This risk can be reduced by not enabling the SQL Server Browser service and by connecting to the instance of SQL Server directly through an assigned TCP port. Connecting directly to an instance of SQL Server through a TCP port is beyond the scope of this article. For more information about the SQL Server Browser server and connecting to an instance of SQL Server, see the following topics in SQL Server Books Online:

• SQL Server Browser Service
• Connecting to the SQL Server Database Engine
• Client Network Configuration