:::: MENU ::::

Posts Categorized / SSMS tips and tricks

  • Jan 24 / 2009
  • 0
dbDigger, Logins and Users, Security and Permissions, SSMS tips and tricks

LogIn failed for user. The user is not associated with a trusted SQL Server connection.

While logging in to your SQL Server instance through SSMS/EM/QA, you may get following error message.
LogIn failed for user ”. The user is not associated with a trusted SQL Server connection.

LogIn Failed For User

In this case you have to change the authentication mode of your that SQL Server instance. Basically SQL Server can authenticate in two modes.

  • Windows authentication mode
  • Mixed mode authentication

If your instance is set to windows authentication mode then while trying to log in through SQL Server user, you will get above error message.
To remove the error and get log in through SQL Server ID, simply change the authentication mode of your SQL Server instance to mixed mode. Follow the following steps for it in SSMS.

Right click on your SQL Server instance and click properties

Go To Properties of Server Instance

A frame will appear with two authentication modes options on it. Change authentication mode to SQL Server and Windows Authentication mode (mixed mode) and click OK.

Set Mixed Mode Security

SQL Server instance restart will be suggested to implement the changes fully.

Configuration Change Confirmation

Restart your SQL Server instance by right clicking it and selecting restart menu item.

Restart SQL Server Instance Service

Now you can log in to this instance successfully. I logged in through SQL Server account id ‘test’.

Connected Through SQL Server Authentication

  • Nov 21 / 2008
  • 0
Date and Time, dbDigger, SSMS tips and tricks, System Functions, T-SQL Scripts

T-SQL DateTime functions to find various dates

Recently i found a very handy collection of various T-SQL DateTime functions to find different dates. I would list those here as it as for my daily use and you may also take benefit of this handy collection.

 --Today  
 SELECT GETDATE() 'Today'  


 --Yesterday  
 SELECT  
 DATEADD(d,-1,GETDATE())  
 AS 'Yesterday'  


 --First Day of Current Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0)  
 AS 'First Day of Current Week'

  
 --Last Day of Current Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)  
 AS 'Last Day of Current Week'  


 --First Day of Last Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)  
 AS 'First Day of Last Week'  


 --Last Day of Last Week  
 SELECT  
 DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6)  
 AS 'Last Day of Last Week'  


 --First Day of Current Month  
 SELECT  
 DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)  
 AS 'First Day of Current Month'  


 --Last Day of Current Month  
 SELECT  
 DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))  
 AS 'Last Day of Current Month'

  
 --First Day of Last Month  
 SELECT  
 DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))  
 AS 'First Day of Last Month'  


 --Last Day of Last Month  
 SELECT  
 DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)))  
 AS 'Last Day of Last Month'  


 --First Day of Current Year  
 SELECT  
 DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)  
 AS 'First Day of Current Year' 

 
 --Last Day of Current Year  
 SELECT  
 DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0)))  
 AS 'Last Day of Current Year'  


 --First Day of Last Year  
 SELECT  
 DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))  
 AS 'First Day of Last Year' 

 
 --Last Day of Last Year  
 SELECT  
 DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))  
 AS 'Last Day of Last Year'  
  • Oct 27 / 2008
  • 0
dbDigger, Hardware and Platform, SSMS tips and tricks

Package ‘Microsoft SQL Management Studio Package’ failed to load

The Error package ‘Microsoft SQL Management Studio Package’ failed to load may be encountered as you try to launch SSMS or visual studio. Mostly occurs after an update for SQL Server or Windows OS. To over come it you may take following steps
  • Verify that the short cut you are using to launch the package has a valid target path
  • If path is OK then run .NET 2.0 framework set up in repair mode
  • If problem persists re install .NET 2.0 framework

The target path may be changed automatically as a result of changed security level. So it would be better to verify it.

  • Oct 06 / 2008
  • 0
dbDigger, SSMS tips and tricks, Transaction log files

Manage Number of SQL Server 2005 Logs archive Files

SQL Server logs files contain valuable information. By default 6 files are archived and any new file removes the oldest one to make its place. In many situations a DBA may require to have more log files archived for a later reference. It is relevant to mention here that a new log file is created every time SQL Srever is restarted.
So how to change the default number i.e. of SQL Server log files

Default Log Files for SQL Server Databases

Go to SSMS
Go to ‘Management folder’
Right click on ‘SQL Server Logs’ folder there
Now you have the interface to manage the number of SQL Server logs files

  • Aug 07 / 2008
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Upgrade SQL Server

change compatibility level of a SQL Server database

I have a database with compatibility level 80 (upgraded from SQL Server 2000 to SQL Server 2005). Today when i tried to disable the trigger through following command

 Use DBNameHere  
 GO  
 DISABLE TRIGGER triggerName  
 ON tablename  
 GO  

Following error occurred

Msg 156, Level 15, State 1, Line 1
 
Incorrect syntax near the keyword ‘TRIGGER’.

It was surprised as the command looked valid and used many times. It was unusual and i had no way but to disable it through SSMS for time being. Later i tried again in a different way and it worked through following command.

 
USE DBNameHere  
 GO  
 ALTER TABLE tableName  
 DISABLE TRIGGER TriggerName  
 GO  

So if you ever get the above mentioned error on enable/disable command then it may be compatibility level issue. Because disable trigger is SQL Server 2005 command. As compatibility level 80 is of SQL Server 2000 so it will not work. If you do not have any problem then you may easily update compatibility level to 90 for upgraded databases.

To change compatibility level of a database to 90 (SQL Server 2005) use following script

 
ALTER DATABASE AdventureWorks  
 SET SINGLE_USER  
 GO  
 EXEC sp_dbcmptlevel AdventureWorks, 90;  
 GO  
 ALTER DATABASE AdventureWorks  
 SET MULTI_USER  
 GO  

Through SSMS you may change the compatibility level by right clicking the database and click properties. Following screen will appear and you may change the level there.

SQL Server dt properties

By the way compatibility levels of SQL Server databases are as followings

* 60 = SQL Server 6.0

* 65 = SQL Server 6.5

* 70 = SQL Server 7.0

* 80 = SQL Server 2000

* 90 = SQL Server 2005

  • Aug 06 / 2008
  • 4
dbDigger, Logins and Users, SET Options, SSMS tips and tricks, T-SQL Scripts

Set SQL Server database to single user mode or multi user mode

Often we require to set the database in to single user mode. And after specified operation we switch to multi user mode. Here is a script to perform the switch operation.
For changing AdventureWorks database to single user mode

 
ALTER DATABASE adventureworks  
 SET SINGLE_USER  
 GO  

and for again to multi user mode

 
ALTER DATABASE adventureworks  
 SET MULTI_USER  
 GO  

Also you have to consider that if some users are connected to that database then their connections will be dropped without any warning. And you can not set the system databases master, tempDB, msDB to single user mode. For that purpose you have to start your SQL Server in single user mode.
You can also set a database to single user mode through GUI. For SSMS i will go through for steps

  • Right click on database to be set user mode
  • Go to Options in left panel
  • In right panel you will found ‘Restrict Access’ option
  • Choose single user mode
  • Click OK

Set single user mode through SSMS

Same procedure would be repeated to set the database back to multiple user mode

Pages:1234567
Consult us to explore the Databases. Contact us