MS SQL SERVER DBA

 Logins are the credentials that authenticate connections to an instance. Except in the case of an instance configured to support contained databases, a database user must map to an existing SQL Server login. You can differentiate SQL Server logins based on the type of authentication method used. SQL Server 2012 supports the following login types:  ■ Windows-authenticated login ■ SQL Server–authenticated login ■ Certificate ■ Asymmetric key  A security principal must have the ALTER ANY LOGIN permission to be able to create SQL logins. Windows-Authenticated SQL Server Logins:             Windows-authenticated SQL Server logins are instance logins in which the operating system handles authentication. You can map a Windows-authenticated SQL Server login to a local user account, a local security group, a domain user account, or a domain security group. To create a Windows-authenticated SQL Server login, ...

This article concentrates on transferring logins between two separate instances of SQL Server 2005. In this article, serverA and serverB are different servers. Additionally, both serverA and serverB are running SQL Server. After we move a database from instance of serverA to the instance of serverB, the users may not be able to log in to the database on serverB. Additionally, the users may receive the following error message: Login failed for user ' MyUser '. (Microsoft SQL Server, Error: 18456) This problem occurs because we don't transfer the logins and the passwords from the instance of serverA to the instance of serverB. To transfer logins we need to follow the steps as below 1) On serverA, start SQL Server Management Studio, and then connect to the instance of SQL Server. 2) Open a new Query Editor window, and then run the following script. +++++++++++++++++++++++++++++++++++++++++++++++++++++++ USE master GO IF OBJE...

Learning something new is challenging. Learning something new on your own redefines challenging. Chances are, you are reading my article because you want to learn how to write stored procedures. You are new to this, and you don’t know where to start. You are exactly where I was when I wanted to learn how to write stored procedures. The purpose of this article is to help the developer who doesn’t know where to start. I will give you a place to start, teach you techniques beyond the basic “hello world”, and walk you through examples. I learned how to write stored procedures alone, and have decided to let you use my experiences to your advantage. This article will consist of the following topics: What are stored procedures? What do you need to get started? Writing your first stored procedure Writing more sophisticated stored procedures Input Variables Input Variables with SELECT and UPDATE Statements Exercise:  Pass Data to a SELECT Store...

The SQL Server "sa" password is default system administrator login to access the database server.  To ensure that this password is secure and not used by unauthorized users. Follow the below steps: Open the SQL Server management studio. Connect to SQL Server using windows authentication. Expand the server and choose security and expand logins. Right click on sa, from properties modify the password and confirm password Impact of change 'sa' password:- If we are using sa login to run DTS packages, changing sa password will affect to run on the DTS package. All the DTS package that has used the sa and it's password. All applications that are using the sa login to perform the activities All scheduled jobs on Server that are used SA password for authentication on the Server. All SQL Server jobs which are owned by sa. All link servers that are used to sa login to connect to the server.

Problem While indexes can speed up execution of queries several fold as they can make the querying process faster, there is overhead associated with them. They consume additional disk space and require additional time to update themselves whenever data is updated, deleted or appended in a table. Also when you perform any data modification operations (INSERT, UPDATE, or DELETE statements) index fragmentation may occur and the information in the index can get scattered in the database. Fragmented index data can cause SQL Server to perform unnecessary data reads and switching across different pages, so query performance against a heavily fragmented table can be very poor. In this article I am going to write about fragmentation and different queries to determine the level of fragmentation. Solution When indexes are first built, little or no fragmentation should exist. Over time, as data is inserted, updated, and deleted, fragmentation levels on the underlying indexes may begin to ri...