1

I'm learning SQL with SQL Server and I've see how to create users, I've created without login:

CREATE USER Luis_Gerente WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Mario_Supervisor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Ana_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Martin_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo
CREATE USER Carlos_Vendedor WITHOUT LOGIN WITH DEFAULT_SCHEMA=dbo

But I don't know what user I'm currently using, and with what user I've created those other users.

How can I know that, and how can I change users depending on the person who is using the database server?

I do authentication at server level but at the database level I currently do not see any effect of having users at database level. When I use the database where I created the users I don't have changes or effects.

When I start to use the database I'm not getting any prompt or form needing a specific database user to use the database with the specific privileges of that user.

Laurenz Albe
  • 51,298
  • 4
  • 39
  • 69
Salvador Morales
  • 13
  • 3

1 Answers1

2

But I don't know what user I'm currently using

CURRENT_USER will return the current username. See https://learn.microsoft.com/en-us/sql/t-sql/functions/current-user-transact-sql for more detail, and links to other similar functions.

As you can see there are a couple of ways to get the current username:
SELECT CURRENT_USER, USER_NAME(), USER_NAME(USER_ID())
should return your name three times.

How can I change users depending on the person who's using the database server?

I think you are confusing database users with server logins, which is easy to do. You have explicitly created those users WITHOUT LOGIN. To let someone login as a specific account you need to create a login then create a database user for that login. Something like:

USE [master]
GO
CREATE LOGIN [NewLoginName] WITH PASSWORD=N'PasswordForLogin'
GO
USE [YourDatabaseName]
GO
CREATE USER [NewUserName] FOR LOGIN [NewLoginName] WITH DEFAULT_SCHEMA=[dbo]
GO

in SSMS. There are numerous other options when creating logins (for things like password policy and so forth) and users. See here and here for MS's documentation.

David Spillett
  • 32,103
  • 3
  • 49
  • 89