In Azure SQL Database and SQL Server, users can be authenticated at the database level without a server-level login. For example, in the context of the Team1 database:
CREATE USER User1 WITH PASSWORD='<complex-password-here>';
Then user must specify the desired database when connecting and the sys.databases catalog view return only the current database. They cannot see other databases.
You can create the user on the databases he is suppossed to have access. You don't have to create the user at master database level, you don't have to create a server login for that user.
Azure SQL Database (and SQL Server also) allows you to create what is called contained users that you can create on the database, and is a login that only exists at the database level but not on the master database. You connect to the specific database where the user is supposed to have access and you create the user as:
CREATE USER User1 WITH PASSWORD='<complex-password-here>';
EXEC sp_addrolemember N'db_owner', N'User1'
GO
Instead of creating the user as shown below on the master database of the Azure SQL logical server.
CREATE USER User1 FOR LOGIN User1
On Azure SQL Database is recommended to create contained users instead of creating logins on the master for later create the user on the user databases based on the login.
Alberto,
When I create User1 I create the user on the desired database as specified in your comment but when I attempt to login via ssms using the newly created user I get the following error:
TITLE: Connect to Server
Cannot connect to abbottazuresqlserver.database.windows.net.
ADDITIONAL INFORMATION:
Login failed for user 'User1'. (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
BUTTONS:
OK
Does the user already have to be authenticated at the server level in order for your suggestion to work?
Currently I'm trying to get my user to login via ssms using the user name and password I provide with SQL Server Authentication.
The only way that I've been able to get the user into the server is via a login.
You need to provide the user and password, then make a click on Options
And specify the database.
Hi, @Alberto Morillo Thanks for your continued contribution.
@Cherise D Woods Thank you for visiting Microsoft QA forums! Please let us know if you find the above reply useful. if yes please do not forget to "Accept the answer" wherever the information provided helps you to help others in the community
Regards
Geetha
Sign in to comment