What permissions should be added to public users sql [closed]

Question

I have a SQL Server and am looking at making it public.

What I mean by this is: I can create a DB and give access to myself and friends etc.

I need to create a login which can only view databases it has access, DISABLE access to stop the server.

I've played around with permissions and settings but every time it blocks the login.

Any advice would be appreciated.

INFO:

SQL Server Express 2017 Windows Server Datacenter 2016

EDIT

I would like have user which can have a database. These users can only access their database.

When I tried, I had access to stop the server. This is not idea for obvious reasons.

So, I need Databases, with logins , which can only access the database theyre assigned to and only read, write etc

Answer 1

I need to create a login which can only view databases it has access, DISABLE access to stop the server.

Contained Databases is what you need.

How it works. It's nomore a server to authenticate you, but a database. You create users bypassing logins, and all what they "see" is limited to databases where they are created.

A contained database basically includes all database settings and the metadata within itself thereby resulting in no configuration dependencies on the instance of the SQL Server Database Engine where the database is actually installed. Users will be able to connect to a contained database without authenticating a login at the Database Engine level. This feature really helps to isolate the database from the Database Engine thereby making it possible to easily move the database from one instance of SQL Server to another. In this tip we will take a look at how to configure and use this feature of SQL Server 2012.

SQL Server 2012 Contained Database Feature

Answer 2

On SQL Server, physical users are database-specific per se. If you want a logical user (a specific person) to only have access to one database, create a login for them, create a user for that login in that database, and grant them whatever minimal rights they need within the database.

For example, earlier today I created a service account for Tableau, to access my data warehouse:

DROP LOGIN Service_Tableau
GO

CREATE LOGIN Service_Tableau WITH PASSWORD = 'swordfish'
GO

USE Warehouse
CREATE USER Service_Tableau FOR LOGIN Service_Tableau
GRANT SELECT ON SCHEMA::Tableau TO Service_Tableau
GO

Anyone who logs in with these credentials will be able to see all data in the Tableau schema of the Warehouse database; no more and no less. They certainly will not be able to shut down the server.

It would be helpful to see what you've run so far - ideally, the actual SQL (with sensitive information redacted, naturally). Can connect with your own credentials, from the machine that is failing to connect with the new user's credentials? If so, that rules out a lot of network config issues.