Using Command Line to Manage SQL Logins
You can administrator logins by using T-SQL statements. This method is usually not as easy as using the user-friendly GUI displays that come with SSMS, but occasionally using T-SQL is better. For example, with installations and upgrades that comprise changes to logins, you can use T-SQL to script the modifications and produce a repeatable process. SQL Server 2012 includes system stored procedures and an ALTER LOGIN declaration that you can use to administrator logins. The similar system stored procedures available in previous versions are still accessible in SQL Server 2012, but they have been denounced and will not be accessible in a upcoming version. Below lists the accessible system stored procedures and the basic function and current state of each one. The state specifies whether the process has been denounced and whether an alternate exists in SQL Server 2012.
| Store Procedure | Function | Status |
|---|---|---|
| sp_addlogin | Add a SQL Server login. | Deprecated; use CREATE LOGIN. |
| sp_defaultdb | Change the default database. | Deprecated; use ALTER LOGIN. |
| sp_defaultlanguage | Change the default language. | Deprecated; use ALTER LOGIN. |
| sp_denylogin | Deny server access to a Windows login. | Deprecated. |
| sp_droplogin | Drop a SQL Server login. | Deprecated; use DROP LOGIN instead. |
| sp_grantlogin | Add a Windows login. | Deprecated. |
| sp_password | Change a login's password. | Deprecated; use ALTER LOGIN instead. |
| sp_revokelogin | Drop a Windows login. | Deprecated; use DROP LOGIN instead. |
The system stored procedures have a selection of parameters, which are recognized in Books Online. Because they have been denounced, they are not the emphasis of this section. Instead, this section emphases on a numeral of examples that utilize the CREATE, ALTER, and DROP statements. The subsequent example makes a SQL Server login with a password that must be changed the first time the login connects:
CREATE LOGIN Larry WITH PASSWORD=N1 mepassw0rd$1
MUST_CHANGE, CHECK_EXPIRATION=ON
You may then use the following ALTER LOGIN statement to alter the default database, language, and password for the new Laura login:
ALTER LOGIN [Laura] WITH
DEFAULT_DATABASE=[AdventureWorks2008],
DEFAULT_LANGUAGE=[British],
PASSWORD=’myStrOngPW’
Finally, you can drop the Laura login by using the following:
DROP LOGIN [Laura]
As you see, the T-SQL statements for Logins are moderately easy to use. To shorten matters, you can generate T-SQL statements from SSMS. To do so, you click the Script button available on the screen that performs after you specify a login action. if you right-click a login and select Delete, the Delete Object screen appears. At the top of this screen is a Script button. When you click this button, SSMS scripts the related T-SQL statements into a queryeditor window for you to review-and-execute.