Security Scenario: Creating login object with User object only in the specific database.

This scenario will create the following only in SQL Server .

- Login object in selected SQL Server instance.

-User Object in specific database

1- Problem

Need to prove that creating Login and user object only not sufficient to let user access to objects of database .

2- Solution

·         Logon to SQL Server by administrator account.

·         Click in the New Query button and typethe following lines.

·         From SQL Server instance open Security folder and then Logins folder

·         Right Click on the Logins folder and then select New Login ... Option

·         The Login - New form will appear.

·         In the General tab of Login - New form type the Login Name 'My_Test_Account'

·         And then choose SQL Server authentication option to enter password and confirm it. type password 'abc'

·         Remove the check of Enforce password policy

·         in the User Mapping tab, check on 'My_Test_Account' Database. this check mean SQL Server will create user account for this Login and will put it in the selected database and it's mean also this login can connect to selected database but until now without any permission to working with selected database objects. the login will not be able to connect to other databases that not check in the User Mapping tab.

·         Then Press OK Button . login now ready to use.

(Login created)

(User created depending on Login)

·         To check that My_Test_Account can access My_Test_DB database, try to Right Click on the My_Test_DB  database and then select Properties .

·         In the Permissions tab, search for Connect permission in the Explicit tab .note that this permission has Grant by default.

·         Before testing the login object, try to check Server authentication that let My_Test_Account Login to access SQL Server .

·         Right Click on Instance Server, and then click Properties.

·         The Server Properties form will appear .

·         Select the Security Tab, and then select SQL Server and Windows authentication mode option.

·         Now click OK button. SQL Server will need to restart the SQL Server Engine, help SQL to do that !.

·         After SQL server restart SQL Server Engine, try to log off from administrator login account.

·         try to logon again by New Login account.

·         Try to browse My_Test_DB Database. it will open successfully .

·         Try to browse any database else My_Test_DB Database, it will make the following message.

·         click on New Query button and then type the following.

it will make the following result.

3-conclusion

In this scenario, the login account without user object in the database can't connect to database . administrator need to make a set of permissions for users objet in the database in order to ensure the ability to use objects of database. The new Login Account with new User Account can connect to database only without any access to any object of that database.