:::: MENU ::::

Posts Categorized / SSMS tips and tricks

  • Oct 30 / 2009
  • 0
DBA best practices, dbDigger, SSMS tips and tricks

Customizing SSMS start up opening panes

Both in SQL Server management studio (SSMS) 2005 and 2008 have option to select from objects like object explorer, new query window or activity monitor to be opened automatically when SSMS starts. Following following couple of steps for this start up customization of SSMS

  • Go to ‘options’ in tools menu
  • Click on ‘environment’ in left frame (if not already selected)
  • Open drop down list in front to ‘At startup’
  • Now choose your required objects and click ok

Selective panes at SSMS startup

Now whenever you will open SSMS, the panes or explorer that you choose would be available automatically. If you choose ‘Open empty environment’ then you would get nothing at SSMS start up, but just SSMS frame without any login frame or any thing else.
Note: The options in drop down may differ slightly in SSMS 2005 and 2008.

  • Sep 09 / 2009
  • 0
dbDigger, Publications of Atif Shehzad on MSSQLTips.com, SSMS tips and tricks

Maximizing work space with SQL Server Management Studio by using separate results tabs

My article related to Maximizing work space with SQL Server Management Studio by using separate results tabs is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Maximizing work space with SQL Server Management Studio by using separate results tabs. It would provide you handy methods and tricks to work in maximized view of SQL Server Management Studio (SSMS).

  • Aug 26 / 2009
  • 1
dbDigger, SQL Server Utilities, SSMS tips and tricks

Where are import Data and export Data wizards in SSMS

I noticed my some colleagues that after migration from SQL Server 2000 to SQL Server 2005, they were having problem to find their favorite data import/export wizard in SSMS. Import data and export data wizards can be accessed by right click on specific database folder in SSMS.

Import data and export data wizards in SSMS

Wizard will be invoked and further steps are same as SQL Server 2000. However we can not save it like DTS package for later use. However in SSMS of express edition, these wizards will not be accessible.

  • Aug 17 / 2009
  • 0
Data Modeling and Database Design, Database Diagrams, dbDigger, Publications of Atif Shehzad on MSSQLTips.com, SSMS tips and tricks

Getting started with SQL Server database diagrams

My article related to Getting started with SQL Server database diagrams is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Getting started with SQL Server database diagrams. It would be very helpful to start effectively working with Database Diagrams in SSMS.

  • Jul 11 / 2009
  • 0
dbDigger, Security and Permissions, SSMS tips and tricks

Grant any permission for all objects in a database through SSMS

Many times it is required to grant any permission for all objects in a database. Or we can say that it is required to grant a database level permission. Consider following tasks for examples

  • To assign EXECUTE permission on all stored procedures and functions to a user.
  • To assign SELECT permission for all tables and views in a database
  • To VIEW DEFINITIONS of all objects in a database
  • To UPDATE all tables and views in a database
  • Top INSERT data in any table or view
  • To DELETE data from any table or view

All these permissions may easily be granted through SQL Server Management Studio (SSMS). It is notable that object level permissions will over ride these database level permissions.
Suppose we have to grant select permissions on all tables and views to User1

  • Open SSMS
  • Right click on database folder on which permissions are required to grant
  • Click properties
  • Click permissions in left panel of frame and also select the user to which permissions are to be granted

Grant database level permissions through SSMS

Here in lower panel you will find about 60 different permissions. Most of these are with self explanatory names. As we are required to grant select permission here on all tables and views to User1. So Click grant for Select and click OK. Task is over and now User1 can select data from any table or view.
If we deny SELECT for any individual table to User1 then deny on object level will have precedence on database level permissions and User1 will not be able to select data from that restricted object.
So you may go through all permissions provided here and use them according to scenario.

  • Jul 02 / 2009
  • 0
dbDigger, SSMS tips and tricks, T-SQL Scripts, Triggers

Enable or disable a DML Trigger through T-SQL and SSMS

I have created triggers to track changed data in various fields of several tables. Such triggers work at table level and are attached with table. While performing schedule updates through SQL Server agent jobs, i disable the triggers in the stored procedure that is performing given updates. It prevents the extra usage of triggers and also optimizes the space by skipping entries by such triggers.
Here is syntax to disable and enable trigger1 on table1

-- Disable the trigger  
 ALTER TABLE table1 DISABLE TRIGGER trigger1  
 GO  
 -- Enable the trigger  
 ALTER TABLE table1 ENABLE TRIGGER trigger1  
 GO  

Similarly another syntax may be used to enable and disable our imaginary trigger trigger1 on imaginary table table1 in this case

 
-- Disable the trigger  
 DISABLE TRIGGER trigger1 on table1  
 GO  
 -- Enable the trigger  
 ENABLE TRIGGER trigger1 on table1  
 GO  

Same task may be performed very easily through SSMS. Suppose trigger trigger1 is created on table table1. And we have to disable and enable it through SSMS

  • Open SSMS
  • Go to table on which trigger is defined
  • Open triggers folder under table

Disable and enable trigger through SSMS

  • Right click on trigger and click on Disable.

Disable trigger through SSMS

Confirmation dialog will be shown and icon of trigger will be slightly changed showing the change in trigger status. To enable the trigger through SSMS, again right click on disabled trigger and click Enable.

Enable trigger through SSMS

Again a confirmation dialog will appear and icon of trigger will be changed reflecting the change in status.

Consult us to explore the Databases. Contact us