:::: MENU ::::

Posts Categorized / SSMS tips and tricks

  • Apr 13 / 2009
  • 0
Data Modeling and Database Design, Database Diagrams, dbDigger, SSMS tips and tricks

This database does not have one or more of the support objects required to use database diagramming

SQL Srever Management Studio is equipped with a comprehensive solution for working with database diagrams. But whenever diagram folder inside a database is clicked for the first time following dialog is shown
This database does not have one or more of the support objects required to use database diagramming

SSMS error for database diagrams

The option dialog just asks your permissions to create some system stored procedures that are required to work with diagrams in SSMS. These stored procedures are not created by default in new databases. So You may allow SQL Server to create these system stored procedures for your database. The system stored procedures will be created in your database after you allow to do so. Following are system stored procedures that are created for working with SQL Server diagrams

  • sp_upgraddiagrams
  • sp_helpdiagrams
  • sp_helpdiagramdefinition
  • sp_creatediagram
  • sp_renamediagram
  • sp_alterdiagram
  • sp_dropdiagram;

Once these system stored procedures are created you will never be prompted again by SQL Server for creating these for that database.

  • Mar 04 / 2009
  • 0
dbDigger, Logins and Users, Security and Permissions, SSMS tips and tricks

Disable log in of a user in SQL Server

It may be required to disable the account of a database user in various scenarios. In SQL Server 2000 we had no such option to disable a database user. From SQL Server 2005 and onwards there is an option to disable the log in of a database user.

  • Under security folder of your SQL Server instance, find and right click the user to whom you want to deny the login permission to SQL Server.
  • Right click the user and click properties
  • Following frame will be displayed, Go to status options

Deny LogIn for SQL Server User

Now click OK and the selected user is no more able to log in to SQL Server.

  • Feb 12 / 2009
  • 0
Backup and Restore, dbDigger, SSMS tips and tricks

how to restore SQL Server backup through SQL Server management Studio(SSMS)

Microsoft has introduced SQL Server Management Studio (SSMS) with SQL Server 2005 release. It is now a favorite tool of SQL Server DBAs. Most of new DBAs and students that are new to SQL Server feel a bit confusion to adopt various facilities for tasks provided in SSMS. I have sketched a step by step configuration for some basic tasks through SSMS.
In this post we will see how to restore SQL Server backup through SQL Server management Studio(SSMS). Follow these steps in your SSMS

Restore SQL Server Database-1

Restore SQL Server Database-2

 

Restore SQL Server Database-3

 

Restore SQL Server Database-4

 

Restore SQL Server Database-5

 

Restore SQL Server Database-6

 

If database already exists then set the option to overwrite it. Make sure that the path you are providing for files in valid path.

 

Restore SQL Server Database-7

 

Click Ok to start restore operation.

  • Feb 11 / 2009
  • 0
DBA best practices, dbDigger, SSMS tips and tricks

how to register new SQL Server instance through SQL Server management Studio(SSMS)

Microsoft has introduced SQL Server Management Studio (SSMS) with SQL Server 2005 release. It is now a favorite tool of SQL Server DBAs. Most of new DBAs and students that are new to SQL Server feel a bit confusion to adopt various facilities for tasks provided in SSMS. I have sketched a step by step configuration for some basic tasks through SSMS.
In this post we will see how to register new SQL Server instance through SQL Server management Studio(SSMS). Follow these steps in your SSMS

Register New Server Instance

Fill our required fields in this frame

Register New Server Instance 1

You may also change default connection properties under connection properties tab.

Register New Server Instance 2

Test the connection parameters through test button and save your settings. New instance would be get registered. Please note that above snaps have been taken from Management studio 2008, so you may get some minor differences in case of SSMS 2005. One of such differences is use custom color option under connection properties tab. This option is not available in SSMS 2005.

  • Feb 10 / 2009
  • 0
dbDigger, SQL Server Services, SSMS tips and tricks

How to Restart SQL Server instance (SQL Srever service) through SQL Server management Studio (SSMS)

Microsoft has introduced SQL Server Management Studio (SSMS) with SQL Server 2005 release. It is now a favorite tool of SQL Server DBAs. Most of new DBAs and students that are new to SQL Server feel a bit confusion to adopt various facilities for tasks provided in SSMS. I have sketched a step by step configuration for some basic tasks through SSMS.
In this post we will see how to Restart SQL Server instance (SQL Srever engine service) through SQL Server management Studio(SSMS). Follow these steps in your SSMS

Re Start SQL Server instance

Click start button and SQL Server instance will restart.

Consult us to explore the Databases. Contact us