SQL Server Login

When you create a SQL Server login (be it either a native SQL Server login with password or a Windows Authenticated login without password), then this user is assigned certain default permissions/privileges. Let's have a step-by-step look at how we can achieve what you want.

1. Create SQL Server Login

USE [master]
GO
CREATE LOGIN [Bob] 
    WITH PASSWORD=N'BobsPass4567#', 
    DEFAULT_DATABASE=[master], 
    CHECK_EXPIRATION=ON, CHECK_POLICY=ON

^{Reference: CREATE LOGIN (Transact-SQL) (Microsoft | Docs | SQL)}

The login has been create for [Bob] and he can already connect to the SQL Server instance.

You may ask yourself: How so?

When a login is created the login receives some default permissions/privileges. Let's see what they are.

2. Check [Bob]'s Permissions/Privileges

In a SQL Server query window execute the following statements/commands:

EXECUTE AS LOGIN = 'Bob'
GO

^{Reference: EXECUTE AS (Transact-SQL) (Microsoft | Docs | SQL)}

With the above step we are posing as [Bob] and are then going to check the default permissions/privileges. Next step:

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL, 'LOGIN') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL,'ROLE') AS fmp
SELECT * FROM sys.fn_my_permissions(NULL, 'USER') AS fmp

^{Reference: sys.fn_my_permissions (Transact-SQL) (Microsoft | Docs | SQL)}

We are checking the default permissions of [Bob] for various so called securable classes (securable_class). We only receive return values for the first statement. These are:

entity_name | subentity_name | permission_name
------------+----------------+-------------------
server      |                | CONNECT SQL
server      |                | VIEW ANY DATABASE

These basic permissions seem reasonable. Allowing the login to connect to the SQL Server instance is a good thing and letting the login view any database seems pretty adequate.

Oh, but you don't want the login [Bob] to be able to view any database? Well let's DENY [Bob] that privilege.

3. DENY Privilege to [Bob]

The following command will deny [Bob] the ability to view any database on the server:

_{You will have to open a new Query Window as your current Query is running as [Bob] and [Bob] can't revoke his own permissions/privileges. Try it....}

DENY VIEW ANY DATABASE TO [Bob]
GO

^{Reference: DENY Server Permissions (Transact-SQL) (Microsoft | Docs | SQL)}

...and because you shouldn't trust anything you read on the internet, you should go ahead and verify that [Bob] can't view any databases:

EXECUTE AS LOGIN = 'Bob'
GO
SELECT * FROM sys.databases
GO

Which returns:

name   | database_id | source_database_id | owner_sid | ...
-------+-------------+--------------------+-----------+-------
master | 1           | NULL               | 0x01      | ...
tempdb | 2           | NULL               | 0x01      | ...

Even though you have denied the VIEW ANY DATABASE privilege to [Bob], he'll still have the ability to view the master and tempdb database. He can't view the msdb or model databases. Why? Well, this topic is worth a separate discussion...

4. GRANT Privileges to Create Databases

[Bob] is now able to connect to the SQL Server instance, but unable to view any database (other than master and tempdb) or perform any other task. But he needs to be able to create databases. Let's assign the right privilege:

GRANT CREATE ANY DATABASE TO [Bob]
GO

Hmmm. Let's see if [Bob] can create a database now:

EXECUTE AS LOGIN = 'Bob'
GO
CREATE DATABASE BobsDatabase
GO

And let's see if [Bob] can view his own database:

SELECT * FROM sys.databases
GO

This returns:

name         | database_id | source_database_id | owner_sid                          | ...
-------------+-------------+--------------------+------------------------------------+---------
master       |  1          | NULL               | 0x01                               | ...
tempdb       |  2          | NULL               | 0x01                               | ...
BobsDatabase | 19          | NULL               | 0x9C426DE3F33E5340A71D96D8635900F0 | ...

So we have proven that [Bob] can create his own databases, but not view all databases.

All these settings and checks will work if you open up a separate query window with [Bob]'s credentials and perform [Bob]'s action in that window, except when modifying his own permissions/privileges.

Folder Access

The folder access issue is a bit more complex. Needless to say, [Bob] doesn't have access to my local C:\SQL\Data\... folder, because [Bob] is a native SQL Server login. However, he does have the CREATE ANY DATABASE privilege, so he doesn't actually require access to the folder itself; the SQL Server instance's service account will do that for him.

Your issue with the folder access might have to do with how you are executing the script in your application. Is it [Bob] that is executing the statement or some unknown application pool account? Does that account have privileges to create any database? Or could you pose as [Bob]?

Hint: EXECUTE AS LOGIN = 'Bob'

This does require that the application pool account have adequate SQL Server privileges. You might have to rewrite that part in your code or seek further advice on how to pose as somebody else.