CREATE ROLE name;

To remove an existing role, use the analogous DROP ROLE command:

DROP ROLE name;

For convenience, the programs createuser and dropuser are provided as wrappers around these SQL commands that can be called from the shell command line:

createuser name
dropuser name

To determine the set of existing roles, examine the pg_roles system catalog, for example

SELECT rolname FROM pg_roles;

The psql program’s \du meta-command is also useful for listing the existing roles.

In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a “superuser”, and by default it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.

Every connection to the database server is made in the name of some particular role, and this role determines the initial access privileges for commands issued on that connection. The role name to use for a particular database connection is indicated by the client that is initiating the connection request in an application-specific fashion. For example, the psql program uses the -U command line option to indicate the role to connect as. Many applications assume the name of the current operating system user by default (including createuser and psql). Therefore it is often convenient to maintain a naming correspondence between roles and operating system users.

The set of database roles a given client connection may connect as is determined by the client authentication setup. Since the role identity determines the set of privileges available to a connected client, it is important to carefully configure this when setting up a multiuser environment.

The post Manage Database Roles appeared first on Hivelocity Hosting.

The post Why should I disable autocommit while dumping a database? appeared first on Hivelocity Hosting.

The installation of Microsoft SQL Server, like that of various modern products is fairly easy, whether you are using a CD called SQL Server Developer Edition, a DVD or a downloaded edition. If you have it on CD or DVD, you can put it in the drive and follow the instructions on the screen as we will review them.

The following steps describe the installation on a Microsoft Windows 2000 Server by the Administrator account, a Windows XP Home Edition, a Windows XP Professional, or the downloaded edition on a Microsoft Windows 2000 Professional.

Log on to your Windows 2000 Server or open Windows 2000/XP Professional
Put the CD or DVD in the drive or download the trial edition of SQL Server
If you are using the CD or DVD, a border-less window should come up (if it doesn’t, open Windows explorer, access the drive that has the CD or DVD and double-click autorun)
If you had downloaded the file, you may have the Download Complete dialog box
In this case, click Open. A dialog box will indicate where the file would be installed
You can accept the default and click Finish. You may be asked whether you want to create the new folder that doesn’t exist and you should click Yes. After a while, you should receive a message indicating success:
Click OK
If you are using the CD installation, click SQL Server 2000 Components or press Alt C.
If you are using the downloadable edition, locate the folder where the files were installed. You should see an autorun icon. Double-click it.
Click SQL Server Components and click Install Database Server
On the first page of the Welcome wizard, click Next
On the second page, choose the type of installation you are performing by clicking the appropriate radio button. The Remote Computer choice would lead you to select a computer or provide its path so you can install SQL Server on a computer that is not close to you. Otherwise, accept or select the Local Computer radio button
After selecting the desired type of installation and if necessary providing the information needed, click Next
The next dialog box allows you to select an installation option such as performing an installation live or scheduling that can be done without your eyewitness supervision (second radio button). For this installation, you should accept the default first radio button and click Next
The next option allows you to type your name (the name you use here has nothing to do with authentication) and optionally your company. After doing this, click Next
Read the Software License Agreement COMPLETELY and, if you agree, click Next. If you don’t agree, click No and stop the whole thing. The next steps assume that you agreed with the small prints
Next, enter the 25-digit CD-Key and click Next
The next step provides other options for the installation type. The first radio button is mainly used if you are installing on a workstation that will be connecting to a server or to servers. The third option allows you to install only MDAC. You should accept or select the default second radio button
After making your selection, click Next.
If possible, accept the Default check box selected on the wizard page.
If you must enter an Instance Name, choose a convenient name and type.
Click Next
The next steps will need some decisions from you. For example, you must now set the amount of installation you want to perform and the folder in which you want to store SQL Server. In most cases, you can accept the default Typical
Click Next
The next step allows you to specify who is doing the installation. If you are an administrator, specify the account’s password. If you have an account that can perform an installation on the server, enter its username, its password, and the domain it belongs to.
Click Next
On the next step, you can accept to exclusively use Windows Authentication with the first radio button. Otherwise, you can and should use this opportunity to allow different persons to log in to this SQL Server. In this case, you can select the second radio button and create a password for the SQL Server Authentication, which would create and setup the sa account
You should never use or allow a blank password, even if you are working alone. For one thing, this would be a bad habit you should not let get to you. For another thing, database security should be one of your biggest concerns and an employer or a job interviewer would be happy to know that you take security issues to your heart and you care. If that’s the case, specify the type of authentication you will use. You should select
Click Next
Once Setup has enough information, on the last wizard page, simply click Next
Once the installation is over, click Finish

SQL Server Startup

To use SQL Server, it must start as a service. You have two options. You can start it every time you want to use. You can also make it start whenever the computer comes up from booting.
To start SQL Server, on the Taskbar, click Start -> Programs -> Microsoft SQL Server -> Service Manager
On the SQL Server Service Manager dialog box, click the Start/Continue button if necessary
On the lower-right corner of the desktop, on the clock section of the Taskbar, the button of SQL Server appears with a green play button .
Close the dialog box

Stopping SQL Server

To stop the SQL Server service, double-click the SQL Server icon on the Taskbar system tray
On the SQL Server Service Manager dialog box, click the Stop button
You will receive a confirmation message box. Click Yes.

Starting the SQL Server Service Automatically

Display the Control Panel window and double-click Administrative Tools
In the Administrative Tools window, double-click Services
In the Services window, scroll to the middle of the right frame and click MSSQLSERVER
On the toolbar, click the Start Service button
Close the Services window

The post Install Microsoft SQL Server appeared first on Hivelocity Hosting.

sp_fulltext_database

Initializes full-text indexing or removes all full-text catalogs from the current database.

sp_fulltext_database [@action =] 'action'
Arguments
[@action =] 'action'

Is the action to be performed. action is varchar(20), and can be one of these values.
enable
Enables full-text indexing within the current database. Important Use carefully. If full-text catalogs already exist, this procedure drops all full-text catalogs, re-creates any full-text indexing indicated in the system tables, and marks the database as full-text enabled.This action does not cause index population to begin; an explicit start_full or start_incremental on each catalog must be issued using sp_fulltext_catalog to populate or repopulate the full-text index.

disable
Removes all full-text catalogs in the file system for the current database and marks the database as being disabled for full-text indexing. This action does not change any full-text index metadata at the full-text catalog or table level.

Return Code Values

0 (success) or 1 (failure)
Result Sets
None
Remarks

Disabling full-text indexing does not remove rows from sysfulltextcatalogs and does not indicate that full-text enabled tables are no longer marked for full-text indexing. All the full-text metadata definitions are still in the system tables. It does indicate that full-text indexing is turned off for the database and no full-text indexing activity can occur.

Permissions

Only members of the sysadmin fixed server role and db_owner fixed database role can execute sp_fulltext_database.

Examples

A. To enable a database for full-text indexing
This example enables full-text indexing for the Northwind database.
USE Northwind
EXEC sp_fulltext_database 'enable'
B. To remove all catalogs from a database
This example disables full-text indexing for the Northwind database.
USE Northwind
EXEC sp_fulltext_database 'disable'

The post Enable the full text indexing for a MSSQL database appeared first on Hivelocity Hosting.

Need to know how you can view a table within a SQL server database? Using the Enterprise Manager, this can be achieved with just a few simple steps.

1. First, you’ll need to open Enterprise Manager and expand the registered SQL Server
2. Expand Databases to see a list of databases on the server
3. Locate and expand the specific database containing the table you wish to view
4. Click on Tables, which will show all of the tables in the database in the pane to the right
5. Locate the table you’d like to open, right-click it and select Open Table -> Return Top…
   
6. Type the number of rows you would like to view and click OK
   
7. If you would like to view all rows in the table, select the Return All Rows option instead

And there you have it!

Popular Links

Looking for more information on SQL? Search our Knowledge Base!

Interested in more articles about Databases? Navigate to our Categories page using the bar on the left or check out these popular articles:

• How to Install SQL Server Management Studio 2017 on Windows Server 2019
• Create a MySQL Backup from Command Line – MySQL Import SQL File
• How to Install SQL Server 2012 Express on Windows Server 2012

Popular tags within this category include: MySQL, MSSQL, phpMyAdmin, PostgreSQL, and more.

Don’t see what you’re looking for? Use the search bar at the top to search our entire Knowledge Base.

The Hivelocity Difference

Seeking a better Dedicated Server solution? In the market for Private Cloud or Colocation services? Check out Hivelocity’s extensive list of products for great deals and offers.

With best-in-class customer service, affordable pricing, a wide-range of fully-customizable options, and a network like no other, Hivelocity is the hosting solution you’ve been waiting for.

Unsure which of our services is best for your particular needs? Call or live chat with one of our sales agents today and see the difference Hivelocity can make for you.

The post View the Contents of a Table in a SQL Server Database Using Enterprise Manager appeared first on Hivelocity Hosting.

Your SQL server must also be set to use TCP/IP as the default protocol. To do so, you will need to run the Client Configuration utility that comes with MSSQL Server.

The post Connect to my MSSQL database via Enterprise Manager? appeared first on Hivelocity Hosting.

Expand SQL Server group

Expand the server name

Expand Databases

Expand the database that you’ll be exporting data from

Click on Tables

Right-click the table you’d like to export, select All Tasks -> Export Data

The first dialog box asks where you’ll be copying the data from, just click Next

The next dialog box asks where you’ll be putting the data. You have a variety of options here, depending on what you’re trying to accomplishing. You can export the data to another SQL Server database, an Oracle database, an Excel spreadsheet, or even a plain text file. Follow the wizard to complete the task and export the data.

The post Export the data from different tables in our shared SQL environment? appeared first on Hivelocity Hosting.

Expand Security.

Click on Logins.

Right-click the user “sa” and select Properties.

Type the new password in the Password field.

Click OK, and you will be prompted to re-type the password.

The post Reset the SQL Server ‘sa’ user password? appeared first on Hivelocity Hosting.

The post Restrict clients using my SQL Server by their IP Address? appeared first on Hivelocity Hosting.

Using Enterprise Manager

To attach a database using Enterprise Manager, follow these instructions:

1. Expand the registered SQL server
2. Right-click Databases, select All Tasks -> Attach Database…
   
3. Click the “…” button to browse for the .mdf file
4. Highlight the necessary .mdf file and click OK
   
5. Click OK again

The database will now show up in Enterprise Manager

Using Query Analyzer

To attach a database for use in Transact-SQL, you’ll need to use the MSSQL Query Analyzer tool. If you’ve never used the Query Analyzer tool, you can follow these steps:

1. First, you’ll need to navigate to the Query Analyzer by going to the Start menu -> All programs -> Microsoft SQL Server -> Query Analyzer
   
2. In the dialogue box marked “Connect to SQL Server” enter the name of your SQL Server as well as your Login name and Password
   
3. Click the OK button
4. This will open an empty window where you can now enter your queries

The following is a series of sample commands based on your files above. You will need to specify the correct directory where these files exist (this example has the files in the C:\SQL directory).

EXEC sp_attach_db @dbname = N’rs_ds’,

@filename1 = N’C:\SQL\rs_ds_dat1.mdf’,

@filename2 = N’C:\SQL\rs_ds_dat3.ndf’,

@filename3 = N’C:\SQL\rs_ds_dat4.ndf’,

@filename4 = N’C:\SQL\rs_ds_dat2.ldf’

And there you have it! Using either of these methods, you now have the ability to restore a database on SQL Server.

Popular Links

Looking for more information on MSSQL? Search our Knowledge Base!

Interested in more articles about Databases? Navigate to our Categories page using the bar on the left or check out these popular articles:

• View the Contents of a Table in a SQL Server Database Using Enterprise Manager
• Create a MySQL Backup from Command Line – MySQL Import SQL File
• How to Install SQL Server 2012 Express on Windows Server 2012

Popular tags within this category include: MySQL, MSSQL, phpMyAdmin, PostgreSQL, and more.

Don’t see what you’re looking for? Use the search bar at the top to search our entire Knowledge Base.

The Hivelocity Difference

Seeking a better Dedicated Server solution? In the market for Private Cloud or Colocation services? Check out Hivelocity’s extensive list of products for great deals and offers.

With best-in-class customer service, affordable pricing, a wide-range of fully-customizable options, and a network like no other, Hivelocity is the hosting solution you’ve been waiting for.

Unsure which of our services is best for your particular needs? Call or live chat with one of our sales agents today and see the difference Hivelocity can make for you.

The post How to Restore a Database on SQL Server Using .mdf, .ldf and .ndf Files appeared first on Hivelocity Hosting.

BACKUP LOG “DATABASE_NAME” WITH TRUNCATE_ONLY

BACKUP LOG “db0098” WITH TRUNCATE_ONLY

This should show you a success message

4. Now change the database seclected in query analyser to “DATABASE_NAME” where DATABASE_NAME is the name of the database which needs modified
5. Fire the command as below:

DBCC SHRINKFILE ('DATABASE_NAME_Log', 1);
DBCC SHRINKFILE ('db0098Log', 1);

where database-name_Log is the name of the log file of the db concerned. Such as test_Log. And the valuew ‘1’ indicates the size that the log file needs to be shrunk to. This can be any value depending on the size of the log file of the db. This value is considered in MB’s.

PS: if you copy and paste the above command in QA, it will throw a syntax error message. You should rather key it in or if you do copy and paste the query, then delet the D at the start of the query and key it in again and do the same for the training bracket. The query will run without trouble then.

The post Shrink MsSql DB Logs appeared first on Hivelocity Hosting.