MS SQL Server

This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.

Mary now has access to this instance of SQL Server 2005, but does not have permission to access the databases. She does not even have access to her default database YourDataBaseName until you authorize her as a database user.

To grant Mary access, switch to the YourDataBaseName database, and then use the CREATE USER statement to map her login to a user named Mary.

To create a user in a database - Type and execute the following statements (replacing computer_name with the name of your computer) to grant Mary access to the YourDataBaseName database.

USE [YourDataBaseName];

GO

CREATE USER [Mary] FOR LOGIN [computer_nameMary];

GO

Now, Mary has access to both SQL Server 2005 and the YourDataBaseName database.

This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This lesson shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.

Now that Mary can access the YourDataBaseName database, you may want to create some database objects, such as a view and a stored procedure, and then grant Mary access to them. A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQL statements that execute as a batch.

Views are queried like tables and do not accept parameters. Stored procedures are more complex than views. Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. It is good programming practice to use stored procedures for all repetitive actions in the database.

This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This answer shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.

As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.

Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.

GRANT EXECUTE ON pr_Names TO Mary;

GO

To remove all database objects created by previous tutorials, you could just delete the database. However, in this tutorial, you will go through the steps to reverse every action you took doing the tutorial.

Removing permissions and objects - Before you delete objects, make sure you are in the correct database:

USE YourDataBaseName;

GO

Use the REVOKE statement to remove execute permission for Mary on the stored procedure:

REVOKE EXECUTE ON pr_Names FROM Mary;

GO

Use the DROP statement to remove permission for Mary to access the YourDataBaseName database:

DROP USER Mary;

GO

Use the DROP statement to remove permission for Mary to access this instance of SQL Server 2005:

DROP LOGIN [Mary];

GO

Use the DROP statement to remove the store procedure pr_Names:

DROP PROC pr_Names;

GO

Use the DROP statement to remove the view vw_Names:

DROP View vw_Names;

GO

Use the DELETE statement to remove all rows from the Products table:

DELETE FROM Products;

GO

Use the DROP statement to remove the Products table:

DROP Table Products;

GO

You cannot remove the YourDataBaseName database while you are in the database; therefore, first switch context to another database, and then use the DROP sta

If you are trying to drop a database that is in use, you will get an error message like this: 'Cannot drop database "GlobalGuidelineData" because it is currently in use.'

Before dropping a database, you must stop all client sessions using this database. If your own client session is using this database, you should set a different database as the current database as shown in this tutorial example:

CREATE DATABASE GlobalGuidelineData

GO

USE GlobalGuidelineData

GO

DROP DATABASE GlobalGuideLineDatabase

GO

Msg 3702, Level 16, State 4, Server LOCALHOSTSQLEXPRESS

Cannot drop database "GlobalGuideLineDatabase" because it is

currently in use.

USE master

GO

DROP DATABASE GlobalGuideLineDatabase

GO

If a database is created with simple CREATE DATABASE statement, the server will create two database files on the hard disk to store data and configuration information about that data bases:

* database_name.mdf - SQL Server Database Primary Data File

* database_name_log.ldf - SQL Server Database Transaction Log File

To find out the location of database files, you can query the "sys.database_files" view as shown in this tutorial example:

USE GlobalGuideLineDatabase

GO

SELECT type_desc, physical_name, size

FROM sys.database_files

GO

type_desc physical_name size

ROWS c:Program FilesMicrosoft SQL Server

MSSQL.1MSSQLDATAGlobalGuideLineDatabase.mdf 152

LOG c:Program FilesMicrosoft SQL Server

MSSQL.1MSSQLDATAGlobalGuideLineDatabase_log.LDF 63

Go verify these two files with Windows Explorer.

If you don't like the default behavior of the CREATE DATABASE statement, you can specify the physical database files with a longer statement:

CREATE DATABASE database_name

ON (NAME = logical_data_name,

FILENAME = physical_data_name,

SIZE = x, MAXSIZE = y, FILEGROWTH = z)

LOG ON (NAME = logical_log_name,

FILENAME = physical_log_name,

SIZE = x, MAXSIZE = y, FILEGROWTH = z)

For example, the following statement will create a database with database files located in the C: emp directory:

USE master

GO

DROP DATABASE GlobalGuideLineDatabase

GO

CREATE DATABASE GlobalGuideLineDatabase

ON (NAME = GlobalGuideLineDatabase,

FILENAME = 'C: empGlobalGuideLineDatabase.mdf',

SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)

LOG ON (NAME = GlobalGuidelineLog,

FILENAME = 'C: empGlobalGuideLineDatabase.ldf',

SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 1MB)

GO

SELECT type_desc, name, physical_name, size

FROM sys.database_files

GO

type_desc name physical_name size

ROWS GlobalGuideLineDatabase C: empGlobalGuideLineDatabase.mdf 1280

LOG GlobalGuidelineLog C: empGlobalGuideLineDatabase.ldf 128

If you are trying to rename a database that is in use, you will get an error message like this: "The database could not be exclusively locked to perform the operation."

Before renaming a database, you must stop all client sessions using this database. Otherwise, you will get an error as shown in this tutorial example:

1. Launch one instance of SQL Server Management Studio and run:

USE GlobalGuideLine

GO

2. Keep the first instance running and launch another instance of SQL Server Management Studio:

ALTER DATABASE GlobalGuideLine

MODIFY NAME = GlobalGuideLineDatabase

GO

Msg 5030, Level 16, State 2, Server LOCALHOSTSQLEXPRESS

The database could not be exclusively locked to perform

the operation.

Obviously, the first instance is blocking the "ALTER DATABASE" statement.

A database is always in one specific state. For example, these states include ONLINE, OFFLINE, or SUSPECT. To verify the current state of a database, select the state_desc column in the sys.databases catalog view. The following table defines the database states.

* ONLINE - Database is available for access. The primary filegroup is online, although the undo phase of recovery may not have been completed.

* OFFLINE - Database is unavailable. A database becomes offline by explicit user action and remains offline until additional user action is taken. For example, the database may be taken offline in order to move a file to a new disk. The database is then brought back online after the move has been completed.

* RESTORING - One or more files of the primary filegroup are being restored, or one or more secondary files are being restored offline. The database is unavailable.

* RECOVERING - Database is being recovered. The recovering process is a transient state; the database will automatically become online if the recovery succeeds. If the recovery fails, the database will become suspect. The database is unavailable.

If you want to move database physical files, you should take the database offline by using the "ALTER DATABASE" statement with the following syntax:

ALTER DATABASE database_name SET OFFLINE

The following tutorial example will bring "GlobalGuideLine" offline:

ALTER DATABASE GlobalGuideLine SET OFFLINE

GO

SELECT name, state_desc from sys.databases

GO

name state_desc

master ONLINE

tempdb ONLINE

model ONLINE

msdb ONLINE

GlobalGuideLine OFFLINE

USE GlobalGuideLine

GO

Msg 942, Level 14, State 4, Line 1

Database 'GlobalGuideLine' cannot be opened because

it is offline.

If you want to move database physical files to a new location, you can use the "ALTER DATABASE" statements to bring the database offline, and link it to the files at the new location. The following tutorial gives you a good example:

ALTER DATABASE GlobalGuideLine SET ONLINE

GO

USE GlobalGuideLine

GO

CREATE TABLE Links (Name NVARCHAR(32))

GO

ALTER DATABASE GlobalGuideLine SET OFFLINE

GO

Now it is safe to move the database physical files to a new location:

1. Run Windows Explorer

2. Create a new directory: c: empdata

3. Drag and drop c: empGlobalGuideLine.mdf to c: empdata

3. Drag and drop c: empGlobalGuideLine.mdf to c: empdata

Go back to the SQL client program and run:

ALTER DATABASE GlobalGuideLine

MODIFY FILE (NAME = GlobalGuideLineDB,

FILENAME = 'C: empdataGlobalGuideLineDB.mdf')

GO

Databases in SQL Server have two update options:

* READ_WRITE - Data objects are allowed to be queried and modified. This is the default.

* READ_ONLY - Data objects are allowed to be queried, but not allowed to be modified.

You can use the "ALTER DATABASE" to change database update options as shown in the tutorial below:

USE GlobalGuideLineDB

GO

INSERT Links (Name) VALUES ('www.rendc.org')

GO

(1 rows affected)

ALTER DATABASE GlobalGuideLineDB SET READ_ONLY

GO

INSERT Links (Name) VALUES ('www.rendc.org')

GO

Msg 3906, Level 16, State 1, Server SQLEXPRESS, Line 1

Failed to update database "GlobalGuideLineDB" because

the database is read-only.

SELECT * FROM Links

GO

Name

www.rendc.org

ALTER DATABASE GlobalGuideLineDB SET READ_WRITE

GO

INSERT Links (Name) VALUES ('www.rendc.org')

GO

(1 rows affected)

As you can see from the output, inserting data into a table is not allowed if the database is in READ_ONLY mode.

Databases in SQL Server have three user access options:

* MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. This is the default.

* SINGLE_USER - One user at a time is allowed to connect to the database. All other user connections are broken.

* RESTRICTED_USER - Only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles are allowed to connect to the database, but it does not limit their number.

You can use the "ALTER DATABASE" to change database user access options as shown in the tutorial below:

USE GlobalGuideLineDatabase

GO

ALTER DATABASE GlobalGuideLineDatabase SET SINGLE_USER

GO

Now connect to server with another client session and try:

USE GlobalGuideLineDatabase

GO

Msg 924, Level 14, State 1, Line 1

Database 'GlobalGuideLineDatabase' is already open and can only

have one user at a time.

Go back to the first session and re-set the database to MULTI_USER:

ALTER DATABASE GlobalGuideLineDatabase SET MULTI_USER

GO