KILL QUERY NOTIFICATION

Remove a query notification subscription from the instance.

Syntax:
KILL QUERY NOTIFICATION SUBSCRIPTION
{ALL | subscription_id }

Key:
ALL Remove all subscriptions in the instance.
subscription_id Remove the given subscription.


This command is silent - does not produce a notification message.

__________________
The MOSS
Master of Solution Service

KILL QUERY NOTIFICATION

Remove a query notification subscription from the instance.

Syntax:
KILL QUERY NOTIFICATION SUBSCRIPTION
{ALL | subscription_id }

Key:
ALL Remove all subscriptions in the instance.
subscription_id Remove the given subscription.


This command is silent - does not produce a notification message.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE LOGIN

Create a SQL Server login account (User).

Syntax
CREATE LOGIN login {WITH PASSWORD = 'password'
[HASHED] [MUST_CHANGE]
[, option_list [ ,... ] ]}

CREATE LOGIN login {FROM sources}

sources:
WINDOWS [ WITH windows_options [ ,... ] ]
CERTIFICATE certname
ASYMMETRIC KEY asym_key

option_list:
SID = sid
DEFAULT_DATABASE = database
DEFAULT_LANGUAGE = language
CHECK_EXPIRATION = {ON | OFF}
CHECK_POLICY = {ON | OFF}
CREDENTIAL = credential

windows_options:
DEFAULT_DATABASE = database
DEFAULT_LANGUAGE = language

__________________
The MOSS
Master of Solution Service

ALTER LOGIN

Change properties of a SQL Server login account.

Syntax
ALTER LOGIN login ENABLE

ALTER LOGIN login DISABLE

ALTER LOGIN login WITH option [ ,... ]

options:
PASSWORD = 'password'
[
OLD_PASSWORD = 'oldpassword'
| password_option [password_option]
]
DEFAULT_DATABASE = database
DEFAULT_LANGUAGE = language
NAME = login
CHECK_POLICY = {ON | OFF}
CHECK_EXPIRATION = {ON | OFF}
CREDENTIAL = credential
NO CREDENTIAL

password_option:
MUST_CHANGE | UNLOCK

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP LOGIN

Remove a SQL Server login account (User).

Syntax
DROP LOGIN login_name

Options
login_name The login to be dropped.

This command will fail if the user is currently logged in or if the login owns any securable, server-level object, or SQL Server Agent job.

Example

DROP LOGIN Billg;
GO

__________________
The MOSS
Master of Solution Service

CREATE MASTER KEY

Create a database master key.

Syntax:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

If password complexity is enforced, the password must be > 8 chars and contain upper/lower case and numeric/ Non-alphanumeric characters.

Back up the master key with BACKUP MASTER KEY

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER MASTER KEY

Alter properties of a database master key.

Syntax:
ALTER MASTER KEY [FORCE] REGENERATE WITH ENCRYPTION BY PASSWORD = 'password'

ALTER MASTER KEY ADD ENCRYPTION BY [SERVICE MASTER KEY | PASSWORD = 'password']

ALTER MASTER KEY DROP ENCRYPTION BY [SERVICE MASTER KEY | PASSWORD = 'password']

REGENERATE - re-create the database master key and all the keys it protects.

FORCE - continue even if the master key is unavailable or the server cannot decrypt all the encrypted private keys.

ADD ENCRYPTION BY SERVICE MASTER KEY - Will store an encrypted copy of the master key in both the current database and in master.

__________________
The MOSS
Master of Solution Service

BACKUP MASTER KEY

Export the database master key.

Syntax:
BACKUP MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Specify the complete UNC or local path, including file name, for the file to which the master key will be exported.

If password complexity is enforced, the password must be > 8 chars and contain upper/lower case and numeric/ Non-alphanumeric characters.

Before the master key is backed up, it must be open/decrypted.
If the master key is encrypted with a password, it must be explicitly opened.
If the master key is encrypted with the service master key, it does not have to be explicitly opened.

Backup the master key as soon as it is created, and store the backup in a secure, off-site location.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

DROP MASTER KEY

Remove the master key from the current database.

Syntax
DROP MASTER KEY

This command will fail if any private key in the database is protected by the master key.

Example

DROP MASTER KEY;
GO

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

RESTORE MASTER KEY

Import a database master key from a backup file.

Syntax
RESTORE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password'
ENCRYPTION BY PASSWORD = 'password'
[FORCE]

FORCE will continue the restore process even if the current database master key is not open, or if some of the encrypted private keys cannot be decrypted.

This encryption/decryption is a resource-intensive operation

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER SERVICE MASTER KEY

Change the service master key of a SQL Server instance.

Syntax:
ALTER SERVICE MASTER KEY [FORCE] REGENERATE


ALTER SERVICE MASTER KEY WITH OLD_ACCOUNT = 'account' , OLD_PASSWORD = 'password'

ALTER SERVICE MASTER KEY WITH NEW_ACCOUNT = 'account' , NEW_PASSWORD = 'password'

ALTER SERVICE MASTER KEY [ { ADD | DROP } ENCRYPTION BY MACHINE KEY ]

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

BACKUP SERVICE MASTER KEY

Backup a service master key.

Syntax:
BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
ENCRYPTION BY PASSWORD = 'password'

Specify the complete UNC or local path, including file name, for the file to which the service master key will be exported.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

RESTORE SERVICE MASTER KEY

Import a service master key from a backup.

Syntax:
RESTORE SERVICE MASTER KEY FROM FILE = 'path_to_file'
DECRYPTION BY PASSWORD = 'password' [FORCE]

During a restore, SQL Server will decrypt all the keys and secrets with the current service master key, and then encrypt them with the restored service master key.
If any one of the decryptions fails, the restore will fail. You can use the FORCE option to ignore errors and replace the service master key, but this option will cause the loss of any data that cannot be decrypted.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE MESSAGE TYPE

Create a new message type.

Syntax
CREATE MESSAGE TYPE message_type [AUTHORIZATION owner]
[VALIDATION = {NONE
| EMPTY
| WELL_FORMED_XML
| VALID_XML WITH SCHEMA COLLECTION collection
}]
[;]

Key:
owner - A database user or role to own the message type.
NONE - The message body may contain any data.
EMPTY - The message body must be NULL
WELL_FORMED_XML - The message body must contain well-formed XML.
WITH SCHEMA COLLECTION - XML that conforms to a schema in the collection specified.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER MESSAGE TYPE

Change properties of a message type.

Syntax
ALTER MESSAGE TYPE message_type
VALIDATION =
{ NONE
| EMPTY
| WELL_FORMED_XML
| VALID_XML WITH SCHEMA COLLECTION schema_collection }
[;]

Key:
NONE - The message body may contain any data.
EMPTY - The message body must be NULL
WELL_FORMED_XML - The message body must contain well-formed XML.
WITH SCHEMA COLLECTION - XML that conforms to a schema in the collection specified.

__________________
The MOSS
Master of Solution Service

DROP MESSAGE TYPE

Drop an existing message type.

Syntax
DROP MESSAGE TYPE mt_name

Key
mt_name The message type to delete.

You cannot drop Server, Database, or Schema names.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

CREATE PARTITION FUNCTION

Create a partition function in the current database.

Syntax
CREATE PARTITION FUNCTION pf_name (input_parameter_type)
AS RANGE [LEFT | RIGHT]
FOR VALUES ( [ boundary_value [,...n] ] )
[ ; ]

Key:
pf_name The partition function to create.
input_parameter_type Data type of the column used for partitioning.
boundary_value The boundary values for each partition
LEFT/RIGHT Which side does the boundary lie

__________________
The MOSS
Master of Solution Service

DROP PARTITION FUNCTION

Drop a partition function from the current database.

Syntax
DROP PARTITION FUNCTION pf_name [;]

Key
pf_name The partition function to drop.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)

ALTER PARTITION FUNCTION

Alter the boundary values for a partition function by splitting or merging the ranges.

Syntax
ALTER PARTITION FUNCTION partition_function()
SPLIT RANGE (boundary_value) [ ; ]

ALTER PARTITION FUNCTION partition_function()
MERGE RANGE (boundary_value) [ ; ]

Key:
SPLIT RANGE Add a partition to the partition function.
MERGE RANGE Drop a partition and merge values to one of the remaining partitions.
boundary_value The range of the new/old partition

__________________
The MOSS
Master of Solution Service

CREATE PARTITION SCHEME

Create a partition scheme in the current database - map the partitions of a partitioned table or index to filegroups.

Syntax
CREATE PARTITION SCHEME partition_scheme
AS PARTITION partition_function
[ALL] TO ( { file_group | [PRIMARY]} [ ,...n ] )
[; ]

Key
partition_function The function using the partition scheme. Must already exist.
ALL All partitions will map to this (one) file group.
file_group Names of the filegroup(s) to hold the partitions. Must already exist.

__________________
The KINGMAKER
Makes Every Thing Possible

Stuffs (My Blog)