Monday, March 11, 2013

Create a user for SQL Server Authentication with MSSQL 2008.

  1. In Microsoft SQL Server Management Studio, the Object Explorer should have expandable areas for Databases, Security, Server Objects, Replication, and Management. Expand "Security" to see Logins, Server Roles, Credentials, Crytographic Providers, Audits, and Server Audit Specifications. Right-click on "Logins" and pick "New Login..." to make a new login.
    • Specify a name at "Login name:"
    • Check the radio button for "SQL Server authentication" and then denote a password.
    • Uncheck the checkboxes for "Enforce password policy" and "Enforce password expiration" and "User must change password at next login" as desired.
    • Pick a database at the "Default database:" dropdown.
    • Click "OK."
  2. Again, beneath "Security" you should see Logins, Server Roles, Credentials, Crytographic Providers, Audits, and Server Audit Specifications. Expand "Server Roles" and then click on "sysadmin."
    • Click "Add..." to bring up the "Select Logins" dialog box.
    • Type the login name you just gave into the field and then click "Check Names" to find it.
    • Click "OK."
    • If you skip this step you will have trouble interacting with the database tables and will likely get an error saying: "The SELECT permission was denied on the object 'extended_properties', database 'mysqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)"
  3. Expand "Databases" to see a list of databases, and then expand a particular database to see Database Diagrams, Tables, Views, Synonyms, Programmability, Service Broker, Storage, and Security. "Security" should be expanded to show Users, Roles, Schemas, Asymmetric Keys, Certificates, Symmetric Keys, and Database Audit Specifications. Right-click on "Users" and pick "New User..." to make a user for the login.
    • A value for "User name:" must be given and I would suggest mirroring the login name.
    • Give the "Login name:" too.
    • Click the button with three periods on it for "Default schema:" to set this value.
    • Check checkboxes for "Owned Schemas" and "Role Members" as desired.
    • Click "OK."

No comments:

Post a Comment