Home » 2012 » August

Monthly Archives: August 2012

SQL Server 2012 || Contained Databases Concept


A Contained Database is a database which contains all the necessary settings and metadata, making database easily portable to another server. This database will contain all the necessary details and will not have to depend on any server where it is installed for anything. You can take this database and move it to another server without having any worries.

Now question raises about the user need on database to login. Contained Database is database that is no more depend on master database for logins. Contained databases contains the user details with database & database user work as login to connect. Once the contained database is moved, the users are moved as well, and users who belong to the contained database will have no access outside the contained database.

In summary, “Database is now self-contained. Database which is ’contained’ will not depend on anything on the server where it is installed.”

Steps:

1) Enable Contained Database

· From SSMS : Steps to enable Contained Database Authentication with SQL Server Management Studio:

1) In Object Explorer, right-click a SQL Server instance, and then click Properties.

2) Select the Advanced page, and in the Containment section, set the Enable Contained Databases to True, and then click OK.

· From T-SQL : Run the following code on SQL Server Denali. This code will enable the settings for the contained database.

sp_configure ‘show advanced options’,1

GO

RECONFIGURE WITH OVERRIDE

GO

sp_configure ‘contained database authentication’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

2) Create Contained Database

CREATE DATABASE [ContainedDatabase]

CONTAINMENT = Partial

3) Create User in Contained Database

· Create SQL User in Contained Database for which login not exits or not already exits

USE [ContainedDatabase]

GO

CREATE USER ContainedUser

WITH PASSWORD = ‘india@1234′;

GO

· Create Windows User in Contained Database

CREATE USER DOMAINUSER

· Create SQL User in Contained Database for which login

USE ContainedDatabase

GO

sp_migrate_user_to_contained

@username = N'<LOGIN>’,

@rename = N’keep_name’,

@disablelogin = N’do_not_disable_login’

@username = N’user : Name of a user in the current contained database that is mapped to a SQL Server authenticated login. The value is sysname, with a default of NULL.

@rename = ] N’copy_login_name | N’keep_name‘ : When a database user based on a login has a different user name than the login name, use keep_name to retain the database user name during the migration.

@disablelogin = N’disable_login | N’do_not_disable_login‘ :disable_login disables the login in the master database. To connect when the login is disabled, the connection must provide the contained database name as the initial catalog as part of the connection string.

4) Try if this user can access Contained Database

We will attempt to login in the database with default settings (Change the database : SSMS> Option > Connection Properties > Connect Database).

You will notice that the login would be successful in the server. When expanded it, the user will have access to the contained database only, and not to any other database.

5) Possible Risk :

· A user can grant and create contained database users within contained database without the knowledge of the administrators

· A user of contained database can gains the access of other database, if these databases have the guest account enabled.

Color Coding for SQL Server Management Studio Status Bar


DBA or Developers both are habitual of using multiple SQL server remotely from one server. But the normal issue of running commands on wrong servers due to less of attention on server name displaying in SSMS query windows pane. We can give different color codes to different server to make them more identifiable in single look. I will help in closing careless mistakes & make query window identification for different server more handy & presentable. Once you have different color for different server in the status bar, it will be easier to notice the server against which they are about to execute the script.

Step 1:

Step 2:

Step 3:

New built-in String functions in SQL Server 2012……..Part 5 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 2 Out 14 explained below.

String functions

· CONCAT : CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string.

CONCAT ( string_value1, string_value2 [, string_valueN ] )

· FORMAT : Use the FORMAT function for locale-aware formatting of date/time and number values as strings. This really a good function because converting a date into desired format with convert along with format number is little uneasy.

FORMAT ( value, format [, culture ] )

New built-in Logical functions in SQL Server 2012……..Part 4 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 2 Out 14 explained below.

Logical functions

· CHOOSE : CHOOSE acts like an index into an array, where the array is composed of the arguments that follow the index argument. The index argument determines which of the following values will be returned.

CHOOSE ( index, val_1, val_2 [, val_n ] )

· IIF : It returns one of the two values depending upon whether the Boolean expression evaluates to either True or False.

IIF ( boolean_expression, true_value, false_value )

New built-in Date and time functions in SQL Server 2012……..Part 3 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 7 Out 14 explained below.

Date and time functions

· DATEFROMPARTS : DATEFROMPARTS returns a date value with the date portion set to the specified year, month and day, and the time portion set to the default. If the arguments are not valid, then an error is raised. If required arguments are null, then null is returned.

DATEFROMPARTS ( year, month, day )

· DATETIMEFROMPARTS : DATETIMEFROMPARTS returns a datetime value. If the arguments are not valid, then an error is raised. If required arguments are null, then a null is returned.

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

· DATETIME2FROMPARTS : DATETIME2FROMPARTS returns a datetime2 value. If the arguments are not valid, an error is raised. If required arguments are null, then null is returned. However, if the precision argument is null, then an error is raised.

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

· SMALLDATETIMEFROMPARTS : SMALLDATETIMEFROMPARTS returns a smalldatetime value. If the arguments are not valid, then an error is thrown. If required arguments are null, then null is returned.

SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

· DATETIMEOFFSETFROMPARTS : DATETIMEOFFSETFROMPARTS returns a datetimeoffset data type. The offset arguments are used to represent the time zone offset. If the offset arguments are omitted, then the time zone offset is assumed to be 00:00, that is, there is no time zone offset. If the offset arguments are specified, then both arguments must be present and both must be positive or negative. If minute_offset is specified without hour_offset, an error is raised. If other arguments are not valid, then an error is raised. If required arguments are null, then a null is returned. However, if the precision argument is null, then an error is raised.

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )

· TIMEFROMPARTS : TIME returns a time value. If the arguments are invalid, then an error is raised. If any of the parameters are null, null is returned. However, if the precision argument is null, then an error is raised.

 TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

· EOMONTH : EOMONTH Returns the last day of the month that contains the specified date.

EOMONTH ( start_date [, month_to_add ] )

New built-in Conversion functions in SQL Server 2012……..Part 2 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. 3 Out 14 explained below.

Conversion functions

· PARSE (Transact-SQL) : PARSE ( string_value AS data_type [ USING culture ] )

string_value : nvarchar(4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type, or PARSE raises an error.

data_type : Literal value representing the data type requested for the result.

Culture : Optional string that identifies the culture in which string_value is formatted.

Use PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Returns the result of the expression, translated to the requested data type.

· TRY_PARSE (Transact-SQL) : TRY_PARSE ( string_value AS data_type [ USING culture ] )

string_value : nvarchar(4000) value representing the formatted value to parse into the specified data type. string_value must be a valid representation of the requested data type, or TRY_PARSE returns null.

data_type : Literal representing the data type requested for the result.

Culture : Optional string that identifies the culture in which string_value is formatted.

Use TRY_PARSE only for converting from string to date/time and number types. For general type conversions, continue to use CAST or CONVERT. Keep in mind that there is a certain performance overhead in parsing the string value. TRY_PARSE relies on the presence of .the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Returns the result of the expression, translated to the requested data type, or null if the cast fails.

· TRY_CONVERT (Transact-SQL) : TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

data_type [ ( length ) ] : The data type into which to cast expression.

Expression : The value to be cast.

Style : Optional integer expression that specifies how the TRY_CONVERT function is to translate expression.

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error. TRY_CONVERT is a new keyword in compatibility level 110. This function is capable of being remote to servers that have a version of SQL Server 2012 and above. It will not be remote to servers that have a version below SQL Server 2012.

New built-in functions in SQL Server 2012……..Part 1 of 5


Microsoft SQL Server 2012 introduces 14 new built-in functions. These functions are divided into 4 categories.

In this article I will be covering usage & functionality of these new 14 functions. I strongly feel, these functions are very useful for developer point of view.

These new functions are:

1) Conversion functions

· PARSE (Transact-SQL)

· TRY_PARSE (Transact-SQL)

· TRY_CONVERT (Transact-SQL)

2) Date and time functions

· DATEFROMPARTS (Transact-SQL)

· DATETIMEFROMPARTS (Transact-SQL)

· DATETIME2FROMPARTS (Transact-SQL)

· SMALLDATETIMEFROMPARTS (Transact-SQL)

· DATETIMEOFFSETFROMPARTS (Transact-SQL)

· TIMEFROMPARTS (Transact-SQL)

· EOMONTH (Transact-SQL)

3) Logical functions

· CHOOSE (Transact-SQL)

· IIF (Transact-SQL)

4) String functions

· CONCAT (Transact-SQL)

· FORMAT (Transact-SQL)

Follow

Get every new post delivered to your Inbox.

Join 228 other followers