Creating a New User Login

SQL Server Administration
1

I can create a login on a server level and assign permission at the database level. Why would one create a login at the database level?

2

"I can create a login on a server level" which server? The OS server or the SQL Server?

3

SQL Server

4

explained here
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authentication-in-sql-server

5

I read that article and I believe it is talking about "authentication". I am asking that we can create a new login at server level of SQL Server which automatically creates at the database level.

Then, would there ever be a need to create it at the database level only, without the server level?

6

It wont be automatically created on database level unless you explicitly give it perms to a database

1 Like
7

Creating a login at the database level is mostly used for availability groups. You can create a user inside the database so it will be available when the database changes nodes. Otherwise, you have to create logins (and matching SIDs and passwords) on each node.

This is also how Azure does SQL databases where you just get a database and not the entire server. Every "login" you create is really a contained database use.

2 Likes
8

Let's clarify something - you don't create a login at the database level. You only create users in a database - and those users can be created without being tied to a login.

There are many reasons why you would create a user without a login - as @graz pointed out you would create users in the primary database that will then tied to a login on the secondary. You could also create users without a login to allow for EXECUTE AS - or tied to certificates or for other database level processing.

1 Like
9

Thank you for all contributions to the question. So basing on the replies, if I create a user at the database level, that does not mean they will be able to access the database without a server login because I put this to the test and the user was not able to get to the database.

Yo, you are right. "It wont be automatically created on database level unless you explicitly give it perms to a database"

10

For a standard SQL Server setup/configuration - then yes. You need a login at the server level and a user in each database with permissions for that database.

A login that is a member of the sysadmin server role does not require a user in any database.

With that said - you can have a contained database where the authentication is actually performed at the database level and therefore you would not have a server level login. See https://docs.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-2017

1 Like