HTML clipboardGoto 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