To access database server from application, you have to create login with access permission for user. So Request raise by application team, they wanted to create SQL server login with limited permission with default database as “appdb” (application database). Request assign to DBA, for creating the login connected to production server by admin login.
Steps involve completing the task
Click on Start – Run (type sqlwb)
This will give you the SQL login window, by using admin account we connected to the production server.
- In Object explorer – Click on Security – Right click on Login – new login
2. Give the SQL Login name and select the authentication mode as SQL Server Authentication. Type the password and confirm password. Allow user to change password after first login.
3. Select the Default database from the drop down list.
4. Click on Server roles from the same window and process admin (as role given the application user)
5. Map the Login with specific the database user and also assign the read permission to production db
6. Give connect permission to the login by clicking on grant option also select the Enabled option to enable the login. We can strict SQL login user to connect server by clicking on the Disabled option. Click on OK
Finally the Login account has been created as per application user request.
Can be done by using T-SQL command
CREATE LOGIN appuser
WITH PASSWORD = ‘XX2Sa@123’, MUST_CHANGE,
DEFAULT_DATABASE = appdb,
CHECK_EXPIRATION = ON,
CHECK_POLICY = ON
By using below query we can drop the existing login.
DROP LOGIN appuser