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

closed as unclear what you're asking by hot2use, mustaccio, LowlyDBA, McNets, Michael Green Dec 6 at 23:59

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Hi, welcome to dba.stackexchange! Could you clarify your question a little bit - specifically, what do you mean by "DISABLE access to stop the server" ? A standard database user by default can't stop the server from within SQL Server, but any Windows Server user with appropriate permissions can stop the service. Preventing a login from viewing dbs is fairly straightforward though. Maybe just clarify exactly what you want to get a good answer. Thanks – Ian_H Dec 6 at 9:19
  • Perhaps you can "aspire" to tidy up the question's spelling and grammar, too? – Michael Green Dec 6 at 23:59

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

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.