Creating a New User in SQL Server

Goto SSMS -> New User. Provide your user name, in my case its sample_user

Choose the authentication and choose the default database.

Default database indicates the database to which the user is having the access. In the next page, you can specify the level of access to the user. The next drop down is Default language -> I have made it as default and it's English.

Arabic, Brazilian, British English, Bulgarian, Croatian, Czech, Danish are some of the languages listed.

In the next window, you can specify the role of the user and it's server role. I have informed that, this person is a sysadmin and public too.

Next option is "User Mapping", here all the database will be listed. You can specify the level of access to be provided to this user for each database.

I do not want to provide any other access to this user except "VenkatDB".

Next screen is securables, you will have list of objects like Server, Endpoints and Logins.

You can choose the object on which you want to provide or deny access.

For example, I have selected Server -> Select an option "Create any database" and -> Select deny option. Here, we have restricted the user to create any database. Still, he is a sysadmin but he can't create any database.

The final page is status. It will define the current state of the user.



Now, let's move to our actual topic.

How to prevent access to a database for a particular SQL Login user????

Consider am having a user who is having access to multiple databases? I want to restrict the user not to use "VenkatDB" - > A specific database.

Now, you need to first make the user as public.

Provide necessary access level in the database level. Make the user as public for that specific database. Now click "OK". Reopen the login page for that particular user. "VenkatDB" is disappeared.

Now, I am trying to access the objects in the database. Below is the message received.

" The server principal "venkat" is not able to access the database "VenkatDB" under the current security context. "

Cheers,

Venkatesan Prabu .J