CODEWITHSUNDEEP
sql-server
Waqas Ali
Waqas Ali

Reputation: 93

SQL Server Restrict Access to Database by Windows Authentication Mode

I am facing problem regarding database permissions. I have created my database named FCProject. I created a login for that database in SQL Server Management Studio. My login is working fine but all I want to restrict access to FCProject database through users who can log in to SQL Server through Windows authentication mode.

Because I want only FC login to be able to view or modify data of FC database in SQL Server Management Studio.

I have tried creating a login through Management Studio. Is there any problem in creating login? Or something else? Please help

Upvotes: 1

Views: 2774

Answers (1)

marc_s
marc_s

Reputation: 755391

You are mixing up two things that should be kept separate here:

  1. A login is on the server level - it gives an account (a Windows account, or a SQL Server specific account) the permission to connect to this server

  2. Each database can then define user for its own use - based on those logins.

So in your case, what you need to do is to:

  1. create a number of logins on the server-level
  2. create users in your FCLogin database for just those logins

and then you're fine - only those accounts that you've defined explicitly can access your FCLogin database and use it.

See the Stairway to SQL Server security series of articles on SQL Server Central (very well worth registering for their contents!) which explains all the ins and outs in great detail

Upvotes: 1

Related Questions