SQL Server Administration - User/Login Admin

Add Login

Creates a new SQL Server login to connect using SQL Server authentication. Note: Microsoft states that this function will be deprecated in future release in favor of using Windows Authentication.

Syntax - SQL Server 2005/2008

sp_addlogin [ @loginame = ] 'login' 
    [ , [ @passwd = ] 'password' ] 
    [ , [ @defdb = ] 'database' ] 
    [ , [ @deflanguage = ] 'language' ] 
    [ , [ @sid = ] sid ] 
    [ , [ @encryptopt = ] 'encryption_option' ] 
[;]
GO

--returns 0=ok 1=failed

Example(s) - SQL Server 2005/2008

Creating a SQL Server login

EXEC sp_addlogin 'ericm', 'bluefin87';
GO

Creating a SQL Server login that has a default database

EXEC sp_addlogin 'ericm', 'bluefin97', 'hcmdatamart';
GO

Misc Notes - SQL Server 2005/2008

SQL Server logins can contain:

• 1 to 128 characters (practically speaking, password should be 6-10 characters in length)
• A-Z, a-z, 0-9
• Symbols are okay except for \
• Password cannot be a reserved login name like sa or public.
• Password must be unique
• Password cannot be NULL or an empty string

 Collapse All    Expand All

• 2005/2008 User Admin
  • Add Sql Server Login
  • Drop Sql Server Login
  • Add User to Database Role
  • Drop User to Database Role
  • Add Login to Server Role
  • Drop Login to Server Role
  • Add Linked Server Login
  • Drop Linked Server Login
  • Grant Login
  • Revoke Login
  • Grant DB Access
  • Revoke DB Access

SQL Server Information and Resources