On SQL Server Management Studio, How to list only the database, which user has access ?

 

The default behavior of SSMS when we login, it will show all the databases available on the SQL Server Instance. Irrespective of access on the databases user has.

The reason behind is, to load the SSMS faster. Otherwise, while loading the SSMS SQL Server has to verify for each databases if the user has access or not. Also if any of the databases or offline or closed, it has to figure out if that database has to be listed or not. All this takes time.
Hence by default the SSMS will show all the databases on SSMS.

If we really required to show only the database user has access, please use the command below to restrict the show all databases on SSMS,

DENY VIEW ANY DATABASE TO login_name

When VIEW ANY DATABASE is revoked, a user can only see master, tempdb, any database he owns, and the user’s current database context.

Impact of renaming “sa” account on SQL Server 2008

 

Error: 912, Severity: 21, State: 2

Microsoft has confirmed that this is a problem in the Microsoft products that are listed 

Microsoft SQL Server 2008 Workgroup

Microsoft SQL Server 2008 Enterprise

Microsoft SQL Server 2008 Developer

Microsoft SQL Server 2008 Standard

This problem was first corrected in SQL Server 2008 Service Pack 1

Cause:

The upgrade process includes a script to create the SP_Syspolicy_Create_Purge_job stored procedure. The procedure creates the syspolicy_purge_history job and hardcodes the owner of the job to be the sa account. Because the sa account is renamed, the job creation fails.

Workaround:  recreate the sa account

Reference:  FIX: Error message when you try to upgrade an instance of SQL Server 2005 to SQL Server 2008: "Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes"

SQL Server 2008 Service Pack 2 - Installation

 

Here Let me show the step by step on SQL Server 2008 SP 2 Installation

 

SQL Server Job fails with error “Unable to open step output file. The step failed”

 

Are you getting the error message below on a SQL Server Job?

Resolution:

See if the SQL Agent Service account have sufficient privileges.

I have encountered this error when I change the SQL Server Agent account.
Resolved giving proper rights on the folder.

Processing order of SELECT Statement

 

The following steps show the processing order for a SELECT statement.

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. WITH CUBE or WITH ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

This is the reason where we can not use column alias on Where clause, however we can use it on Select / Order by.

If interested on the logical order of the query processing poster, download it from here.

Reference: http://msdn.microsoft.com/en-us/library/ms189499.aspx

SQL Server 2008 Service account Privileges

 

Please refer the http://msdn.microsoft.com/en-us/library/ms143504.aspx 
on setting up Windows Service Accounts.

It discuss about the

• Domain User Account
• Local User Account
• Local Service Account
• Network Service Account
• Local System Account
  
• Windows NT Rights and Privileges Granted for SQL Server Service Accounts
• Access Control Lists Created for SQL Server Service Accounts
• Windows Permissions for SQL Server Services

Can we have SQL Server 2000 default Instance and SQL Server 2005 default Instance on the same server?

The default instance could be an installation of

SQL Server 2000,
SQL Server 2005,
SQL Server 2008, or
SQL Server 2008 R2.

Only one installation of SQL Server, regardless of version, can be the default instance at one time