- Changes the owner of the current database.
After sp_changedbowner is executed, the new owner is known as the dbo user inside the database. The dbo has implied permissions to perform all activities in the database.
The owner of the master, model, or tempdb system databases cannot be changed.
To display a list of the valid login values, execute the sp_helplogins stored procedure.
Executing sp_changedbowner with only the login parameter changes database ownership to login and maps the aliases of users who were previously aliased to dbo to the new database owner.
Only members of the sysadmin fixed server role can execute sp_changedbowner.
Example
This example makes the user Emil the owner of the current database and maps
existing aliases to the old database owner to Emil.
EXEC sp_changedbowner 'Emil'
Thursday, February 28, 2008
Wednesday, February 27, 2008
Overview of DTS Package
DTS Introduction
Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.
DTS Basics
Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.
DTS supplies a number of tasks that are part of the DTS object model that can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:
Importing and exporting data
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
Transforming data
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
Copying database objects
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Sending and receiving messages to and from other users and packages
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
Executing a set of Transact-SQL statements or Microsoft ActiveX scripts against a data source
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.
Many organizations need to centralize data to improve corporate decision-making. However, their data may be stored in a variety of formats and in different locations. Data Transformation Services (DTS) addresses this vital business need by providing a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations supported by DTS connectivity. By using DTS tools to graphically build DTS packages or by programming a package with the DTS object model, you can create custom data movement solutions tailored to the specialized business needs of your organization.
DTS Basics
Data Transformation Services (DTS) provides a set of tools that lets you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You create a DTS solution as one or more packages. Each package may contain an organized set of tasks that define work to be performed, transformations on data and objects, workflow constraints that define task execution, and connections to data sources and destinations. DTS packages also provide services, such as logging package execution details, controlling transactions, and handling global variables.
DTS supplies a number of tasks that are part of the DTS object model that can be accessed graphically, through DTS Designer, or programmatically. These tasks, which can be configured individually, cover a wide variety of data copying, data transformation, and notification situations. For example:
Importing and exporting data
DTS can import data from a text file or an OLE DB data source (for example, a Microsoft Access 2000 database) into SQL Server. Alternatively, data can be exported from SQL Server to an OLE DB data destination (for example, a Microsoft Excel 2000 spreadsheet). DTS also allows high-speed data loading from text files into SQL Server tables.
Transforming data
DTS Designer includes a Transform Data task that allows you to select data from a data source connection, map the columns of data to a set of transformations, and send the transformed data to a destination connection. DTS Designer also includes a Data Driven Query task that allows you to map data to parameterized queries.
Copying database objects
With DTS, you can transfer indexes, views, logins, stored procedures, triggers, rules, defaults, constraints, and user-defined data types in addition to the data. In addition, you can generate the scripts to copy the database objects.
Sending and receiving messages to and from other users and packages
DTS includes a Send Mail task that allows you to send an e-mail if a package step succeeds or fails. DTS also includes an Execute Package task that allows one package to run another as a package step, and a Message Queue task that allows you to use Message Queuing to send and receive messages between packages.
Executing a set of Transact-SQL statements or Microsoft ActiveX scripts against a data source
The Execute SQL and ActiveX Script tasks allow you to write your own SQL statements and scripting code and execute them as a step in a package workflow.
Thursday, February 21, 2008
SQL Server 2008 New DATETIME DataTypes
The DATETIME function’s major change in SQL Server 2008 is the four DATETIME data types introduced. They are DATE, TIME, DATETIMEOFFSET and DATETIME2. IN addition to these newly introduced data types, there are new DATETIME functions all well.
TIME Datatype
DECLARE @dt as DATE
SET @dt = getdate()
SELECT @dt
23:48:04.0570000
The range for the DATE datatype is from 00:00:00.0000000 through 23:59:59.9999999..
DATETIME2 Data Type
DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
SELECT @dt7
2007-10-28 22:11:19.7030000
for above example, (7) is the Fraction
Fraction Output
0 2007-10-28 22:11:20
1 2007-10-28 22:11:19.7
2 2007-10-28 22:11:19.70
3 2007-10-28 22:11:19.703
4 2007-10-28 22:11:19.7030
5 2007-10-28 22:11:19.70300
6 2007-10-28 22:11:19.703000
7 2007-10-28 22:11:19.7030000
Monday, February 4, 2008
Maps an existing database user to a SQL Server login using SP_CHANGE_USERS_LOGIN
Automatically mapping a user to a login, creating a new login if it is required
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.
EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'
- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.
AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction
The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password 123456 if the login Emil does not exist.
EXEC SP_Change_Users_Login 'Auto_Fix','Emil',Null,'123456'
- it is useful when u restore a existing DB to new Server. After u restore the DB, user in DB cannot link to SQL Server Login. So, you need to use the sql command instead of SQL Server GUI to update user Access Right.
AUTO_FIX
Auto_FixLinks a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
extra
Use sp_change_users_login to link a database user in the current database with a SQL Server login. If the login for a user has changed, use sp_change_users_login to link the user to the new login without losing user permissions. The new login cannot be sa, and the user cannot be dbo, guest, or an INFORMATION_SCHEMA user.
sp_change_users_login cannot be executed within a user-defined transaction
Identify your SQL Server version and edition
This article show you your current Microsoft SQL Server version number and the corresponding product or service pack (SP) level. It also describes how to identify the specific edition Microsoft SQL Server.
SELECT @@VERSION
Result - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Result - 8.00.2039 , SP4 , Standard Edition
SELECT @@VERSION
Result - Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
Result - 8.00.2039 , SP4 , Standard Edition
Subscribe to:
Posts (Atom)