Creating, Updating and Deleting SQL Server 2005 Logins

With the introduction of new login types in SQL Server 2005, the creation, modification, and deletion of logins has changed a little. This article discusses the creation, modification and deletion of logins in SQL Server 2005.

SQL Server 2005 still uses the original three types of logins from the prior versions of SQL Server. It also adds new logins that are asymmetric key and certificate specific.

Login types under the new SQL Server 2005 are:

  • SQL Server login.
  • Windows Group login.
  • Certificate based login.
  • Asymmetric keys based login.
  • Windows User login.

SQL Server Login – This is the good old method to login in to SQL Server. In this case, the username and password both, are stored in the master database. The SQL Server checks and verifies the login internally. The concept remains the same in SQL Server 2005, but the problems that were identified with the administration of passwords of SQL Server logins, is done away with, and now the passwords can be set to expire after a pre-determined period.

Windows User and Group Logins – Nothing has changed in SQL Server 2005 as compared to previous versions. Such types of login process remains the same.

Certificates and Asymmetric Key based SQL Server logins – If and when a Certificate or an asymmetric key is stored in SQL Server 2005, it can be used as a login.

SQL Server 2005 and the new login management – SQL Server 2005, unlike it predecessors, considers logins as entities or objects, which are similar to tables, view, etc. Hence, SQL Server 2005 uses T-SQL commands to manage SQL Server 2005 Logins.

These commands are just like the commands used for creating tables:

  • CREATE LOGIN
  • ALTER LOGIN
  • DROP LOGIN

Albeit, the old stored procedures to create new logins still work, but it would be best to use the new T-SQL commands, shown below, to create new logins, as the old stored procedures are being deprecated.

Syntax to create Logins in SQL Server 2005:

SQL Server Logins:

CREATE LOGIN name { WITH PASSWORD=’pass’ [[HASHED] [MUST_CHANGE] [,more options]] | FROM source }
 
Other options include
 
SID = SID
DEFAULT_DATABASE = default database
DEFAULT_LANGUAGE = default language
CHECK_EXPIRATION = { ON | OFF}
CHECK_POLICY = { ON | OFF}
CREDENTIAL = credential name
 
A Create SQL Server login example
 
CREATE LOGIN MyLogin
WITH PASSWORD = 'h62a2127a' MUST_CHANGE,
DEFAULT_DATABASE = LearnSQL,
CHECK_EXPIRATION = ON,

Windows User and Group Login Syntax:
 
CREATE LOGIN name {FROM WINDOWS [WITH [options]]}
 
The “FROM WINDOWS” clause in the above syntax accounts for, security logins, Windows User, and Windows Group logins.
 
A CREATE windows User and Group Login example-
 
CREATE LOGIN [MyCompUsers]
FROM WINDOWS
WITH DEFAULT_DATABASE = LearnSQL

Deleting logins in the New SQL Server 2005 is fairly easy and simple. The syntax is given below:

DROP LOGIN username

Beware that the above statement will delete a user, which would basically mean that the user will deleted irrespective, if the user is associated with any databases, even if the user is a DBA of any of the databases within SQL Server 2005.

Altering Logins in SQL Server 2005 – There are two approaches to altering or modifying logins in SQL Server 2005, and both of these approaches use the ALTER LOGIN T-SQL command.

Syntax for Altering Logins in SQL Server 2005 – ALTER LOGIN username { STATUS | WITH option [, } }

The first approach would be to ENABLE/DISABLE logins in SQL Server 2005. When the status is set to “enable”, it would the specified username to login into SQL Server 2005, and enabling “disable” wouldl disallow the username to login.

Example of Alter Login, using ENABLE/DISABLE

ALTER LOGIN myLogin DISABLE

Prior versions of SQL Server, do not allow “Enable” or “Disable” logins.

The Several options used with the ALTER LOGIN command include:

PASSWORD=’new changed password’

For example – ALTER LOGIN myLogin WITH PASSWORD = ‘newchangepassword’ UNLOCK

Therefore, SQL Server 2005, is backward compliant, and does keep the familiar ways of storing and managing logins, but adds a few new, but more secured lof-ins.

You may also like:

Be the first to know about new gadget releases, reviews, deals and giveaways.

No spam. We hate spam too.

Tablet PC deals and Promotion