SQL Server BasicsWhat is SQL Server database?What is RDBMS?What is Normalization?Why we use Denormalization?What_is_SQL?What is PL/SQL?Difference between SQL and PL/SQLDatabase TableOne to One RelationshipOne to Many RelationshipMany to Many RelationshipMany to One RelationshipString Data TypesNumber Data TypesDate Data TypesOther Data TypesCreate DatabaseDrop DatabaseCreating and Managing Users in SQL ServerCreate TableAlter TableDrop TableConstraints in SQL serverPrimary KeyForeign KeyUnique KeyCandidate KeyComposite KeyDifference between primary key and candidate keyPrimary key and foreign key relationshipSurrogate KeyCascading Referential Integrity ConstraintsSelf Referential Integrity ConstraintsInsert into statementInsert multiple recordsUpdate statementDelete statementTruncate statementDifference between Delete and TruncateAlias in SQL ServerSelect statementSelect DistinctSelect TopSelect IntoNull Functions(ISNULL(),NULLIF(),COALESCE())Sub QueryIdentity ColumnSequence objectDifference between sequence and identity columnSQL Server ClausesWHERE ClauseOrder By ClauseTop N ClauseGroup By ClauseHaving ClauseDifference between Where and HavingSQL Server OperatorsArithmetic OperatorsComparison OperatorsLogical OperatorsBitwise OperatorsAny OperatorsAll OperatorsUnion OperatorsUnion All OperatorsDifference between Union and Union AllIntersect OperatorExcept OperatorDifference between Except and IntersectJoinsInner JoinLeft JoinRight JoinFull JoinSelf JoinCross JoinViewsWhat are views?Create views using SSMSIndexed ViewsComplex ViewsCheck Option in ViewCheck Encryption in ViewSchema Binding Option in ViewRead-only ViewsUpdatable ViewsAdvantages and disadvantages of viewsCreate multiple views on one tableCan we implement index on views?Can we Perform Insert, update, delete operation on views?Stored Procedure and FunctionsWhat are Stored Procedures?Why we use stored procedures?Passing parameters to Stored procedureUser-Defined FunctionsDifference between UDF and Stored procedurePre-Defined Functions@@Indentity and Scope_IndentityNULLIF, ISNULL and COALESCE
Previous
Next

How to create and manage users in SQL Server.

To create and manage users in SQL Server, you can use SQL Server Management Studio (SSMS) or execute SQL commands using a query window. Here's a step-by-step guide on how to create and manage users:

Creating a User:

  1. Open SSMS and connect to your SQL Server instance.
  2. In the Object Explorer pane, expand the Security folder.
  3. Right-click on the "Logins" folder and select "New Login."
  4. In the "Login - New" dialog box, specify the login name for the user in the "Login name" field.
  5. Choose the authentication method (Windows Authentication or SQL Server Authentication) based on your requirements.
    • For Windows Authentication, enter the Windows username or group name in the "Enter the object name to select" field.
    • For SQL Server Authentication, enter the desired password in the "Password" and "Confirm password" fields.
  6. Specify the default database for the user in the "Default database" field.
  7. Configure any additional options, such as server roles, default language, and default schema, as needed.
  8. Click the "OK" button to create the user.
createUser.png

Managing User Permissions:

  1. Open SSMS and connect to your SQL Server instance.
  2. In the Object Explorer pane, expand the Security folder.
  3. Expand the "Logins" folder and locate the user you want to manage.
  4. Right-click on the user and select "Properties."
  5. In the "Login Properties" dialog box, you can modify various settings for the user, including password, default database, server roles, and more.
  6. To manage user permissions at the database level:
    • Expand the Databases folder in the Object Explorer pane.
    • Expand the specific database in which you want to manage permissions.
    • Expand the Security folder.
    • Right-click on the "Users" folder and select "New User."
    • In the "Login name" field, enter the login name of the user you want to create.
    • Specify the user type, default schema, and other relevant settings.
    • Click the "OK" button to create the user.
  7. To manage user permissions within a database:
    • Right-click on the user within the specific database and select "Properties."
    • In the "User Mapping" section, you can assign roles and permissions to the user for that specific database.
    • Select the desired database from the "Users mapped to this login" section.
    • In the "Database role membership for:" section, assign roles to the user by selecting the appropriate checkboxes.
    • In the "Database role membership for:" section, you can also specify explicit permissions for the user by selecting the "Owned Schemas" or "Database role membership" checkboxes and making the necessary selections.
    • Click the "OK" button to apply the changes.
createUser2.png

By following these steps, you can create users in SQL Server and manage their permissions at both the server and database levels.

Previous
Next