Wednesday, October 20, 2010

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.

Thursday, October 14, 2010

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

    image

    image

    image

    image

    image

    image

     

    image

    image

    image

    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?

    image

    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.

    Saturday, October 09, 2010

    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

    Thursday, October 07, 2010

    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

    Wednesday, October 06, 2010

    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