I am merging a few instances together which are for separate customers. They need to be separated by account access so customer X can't view customer Y's databases.

I have created a login (we'll call it Bob). Bob has the server roles dbcreator and public. Under user mapping, I have given access to the required databases.

If I use these account details in SSMS, I can see all of the databases (as If I were SA). I can only open the database I have access to but I still don't want Bob to be able to see other customers' database names. Any ideas?

Also, when using a script in my application to create a new database, it complains that I do not have access to C:\Program Files\Microsoft SQL Server\MSSQL12.IDENTITYACCESS\MSSQL\DATA

My instance has access to this folder but I do not want to give Bob the sysadmin server role. Is there a way to give Bob access to that folder without giving the sysadmin server role?

  • 1
    You can consult this my answer: dba.stackexchange.com/questions/224260/… to create users that will "see" only the databases they access – sepupic Dec 6 at 12:34
  • 1
    >>>it complains that I do not have access<<< Are you sure it complains that YOU have not access? It is SQL Server service account that should have the permissions to write in this folder – sepupic Dec 6 at 12:51
  • What sepupic said will probably be the best solution. You could do some stuff like altering the public server role with: "USE MASTER GO DENY VIEW ANY DATABASE TO PUBLIC", but this will mean that every one that only has the public server role will not see any databases. However, the db owner will be able to see its own database, so if you have a 1 on 1 mapping on user <> database, you could try that. – Randi Vertongen Dec 6 at 13:15
  • @RandiVertongen okay that makes sense. I'll give that a go. How will this affect the SA account? As I still want to be able to view everything from there. Cheers. – Shaun5 Dec 6 at 13:25
  • No problem, the SA account will still have access – Randi Vertongen Dec 6 at 13:36
up vote 2 down vote accepted

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.

  • Exactly what I'm after. Thanks so much for taking the time to write this! – Shaun5 Dec 6 at 14:55
  • 1
    The other answer does suggest exactly the same, to DENY VIEW ANY DATABSASE and to make Bob owner of it's own db. And it also warns you that in case when MORE THAN ONE user should access the same database, only one of them will "see" this database – sepupic Dec 6 at 15:19
  • 2
    @sepupic Yes, however, this went into far more detail and explained what is actually going on as well as how to prove the changes. Thanks – Shaun5 Dec 6 at 15:23
  • @sepupic That is correct. Only database owners are able to see their own database(s). And only sysadmins are then allowed to see all databases. – hot2use Dec 6 at 15:32
  • In my opinion, on the contrary, this answer does NOT explain why WILL Bob "see" newly created database...And he will only because it 'll become OWNER of this database, and the owner can be one and only one. Sincerely, I've NEVER seen any production database where the user is one only, maybe only in case when it's not a human but some application, so it cannot be a solution in general case. The other answer underlines this, under "If both the statements below are true..." – sepupic Dec 6 at 15:32

If both the statements below are true

-You have a 1 on 1 relationship with User - Databases (or one to many)

-The Users are allowed to be the database owner (and do anything in their own database).

Step 1 Deny all public logins the right to see db's

  USE MASTER
    GO
    DENY VIEW ANY DATABASE TO PUBLIC
    GO

Source

Step 2 Add Bob as the owner of his database

USE BOBDB
exec SP_changedbowner BOB

Full example

DENY VIEW ANY DATABASE TO PUBLIC

USE [master]
GO
CREATE LOGIN [BOB] WITH PASSWORD=N'SecureP@ssword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Create database BOBDB
go

USE BOBDB
exec SP_changedbowner BOB

Result for BOB:

enter image description here

Drawbacks:

1) Bob will be owner of his own database, and can drop tables, or even the database itself.

2) You can only have one owner per DB

3) ...

  • 1
    Appreciate the response! – Shaun5 Dec 6 at 14:56
  • No problem, the other answer was way more thorough ,just wanted to add some risks – Randi Vertongen Dec 6 at 15:31

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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