sql-serverauthentication

SQL Server Logins and Users

I want to create a collection of databases and users so that each user has access to only one database.

The SO question Difference between a User and a Login in SQL Server discusses Logins and Users.

I understand from this that generally a login is for the server and a user is for the database. However, I can’t see how that works, as the password is set for the login, not the user.

Does that mean that I need a different login for each database, or is there a way of doing this with a single login with multiple users, each with their own password?

I want to do this in SQL, not the gui interface, since I need to script this.

Solution

  • Does that mean that I need a different login for each database, or is there a way of doing this with a single login with multiple users, each with their own password?

    If you're using SQL Logins then each individual will need their own LOGIN, yes, which will then need it's own USER created in the respective database(s) that person needs access to. As an overly simplified example:

    CREATE DATABASE SteveDB;
CREATE DATABASE JaneDB;
GO

CREATE LOGIN Steve WITH PASSWORD = 'ABC123!"£', DEFAULT_DATABASE = SteveDB;
CREATE LOGIN Jane WITH PASSWORD = 'XYZ789*()', DEFAULT_DATABASE = JaneDB;
GO
USE SteveDB;
GO
CREATE USER Steve FOR LOGIN Steve;
ALTER ROLE db_owner ADD MEMBER Steve; --Example demonstration of giving them high permissions. You may well want them to have lower permissions
GO
USE JaneDB;
GO
CREATE USER Jane FOR LOGIN Jane;
ALTER ROLE db_owner ADD MEMBER Jane;
GO

    If you are using contained databases, then you can "skip" the LOGIN creation, and instead create a USER with a password instead. The person authenticating to the SQL Server, however, would need to ensure that the specify the database they want to connect to when authentication to though, as not doing so would result in a connection error. Note, however, that contained databases do work a little differently to non-contained; the biggest thing people seem to "forget", is cross-database queries are not permitted in such databases.

    Using the prior simplified example:

    CREATE DATABASE SteveDB CONTAINMENT = PARTIAL;
CREATE DATABASE JaneDB CONTAINMENT = PARTIAL;
GO
USE SteveDB;
GO
CREATE USER Steve WITH PASSWORD = 'ABC123!"£';
ALTER ROLE db_owner ADD MEMBER Steve;
GO
USE JaneDB;
GO
CREATE USER Jane WITH PASSWORD = 'XYZ789*()';
ALTER ROLE db_owner ADD MEMBER Jane;
GO