Why normal db user has access to master db?

_{Thursday, November 15, 2012 2:52 PM}

Hi

I am using SQL Server Express 2012.

I have created new login and new db user fot this login using this commands:

create login [logintest] with password = 'mypass'

create user [usertest] for login [logintest]

I have connected to my server by MSSMS on logintest and I can display all data from master table. E.g. I can execute this select * from [master].[sys].[all_views]

Why normal user without any additional privileges can browse data in master db?

I thought this it should be not allowed.

Can this user also add/change/delete some data in master db?

I do not want try this because I do not want break my server.

Regards

kicaj

_{Friday, November 16, 2012 8:43 AM ✅Answered}

Hi kicaj,

First, you can check the system views property, under the permissions tab, we can find that public database role group has select permission, every user is a member of public database role, so [usertest] can get the view names and query data from the view. View is a defined query statement, when one user query data from view, he/she cannot get data beyond his/her permission. Just for testing, we login SQL Server with a sysadmin user account can create a table named “Table_1”:

use master;
go

create table dbo.[Table_1]
(
    ID int,
    Name varchar(20)
)

when executing the following codes (sys.tables is one of the system views):

select * from sys.tables;

We found “Table_1” is listed. Then login SQL Server with a public server role, and execute the codes again, “Table_1” doesn’t list. So we don’t need to worry that low privilege uses will get higher privilege user information via these system views. After testing, please drop the table with the following codes:

use master;
go

drop table dbo.[Table_1];

Allen Li

TechNet Community Support

_{Thursday, November 15, 2012 2:54 PM}

Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. 

_{Thursday, November 15, 2012 2:58 PM}

Not sure, whether that's the complete script, but it looks like you have create the user in the master database.

use [master];
go
create login [logintest] with password = 'mypass';
go
create user [usertest] for login [logintest];
go

I think, you had used an administrative account to connect to SSMS, and open a query window. Normally this query windows opens in master database context, otherwise specified. To create an user in the user database, modify the script as mentioned below:

use [master];
go
create login [logintest] with password = 'mypass';
go

use [myuserdb];
go
create user [usertest] for login [logintest];
go

SKG: Please Marked as Answered, if it resolves your issue. (b:http://sudeeptaganguly.wordpress.com )

_{Thursday, November 15, 2012 3:00 PM}

create user [usertest] for login [logintest]

Hello kicaj,

Have you created the user in the "master" database? That's not necessary.

See sys.all_views (Transact-SQL) => Permission. Yes, every user can query e.g. "all_views", but in the result the user will only see those views, where he have permissions for. If the user don't have any permission on any view, he will always get an empty result. So where do you see a security issue here?

Olaf Helper

Blog Xing

_{Thursday, November 15, 2012 3:05 PM | 1 vote}

Hi,

If you don't want any people in the admin group on the server to be able to access the database, then remove the "BUILTIN\Administrators" user on the server. And make it password protected.

How to allow data update only thru application ...and not thru sql tools :

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/342ede90-5878-4798-86ec-79e4fe8b91d9/

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

_{Thursday, November 15, 2012 5:09 PM}

Since you are using SQL Server 2012 you might be interested in using the new Contained Databases http://msdn.microsoft.com/en-us/library/ff929071.aspx feature. This allows you to add a user in a specific database, but not create a login. And the user will have no access to master. There are some security impacts (database owners can add new users without sysadmins creating logins). And there are some extra steps (enabling contained databases, and adding the database to the connection strings). However there are also benefits in addition to the one you want.

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

_{Thursday, November 15, 2012 9:17 PM | 1 vote}

I have repeated the test so I executed this:

use my_db

create login [logintest1] with password = 'mypass'

create user [usertest1] for login [logintest1]

Next I have run new instance of MSSMS and I logged in as logintest1.

I can expand nodes for system databases: “master”, “msdb”, “tempdb” but there is exception “model” db – here I cannot expand nodes in the Object Explorer (I got error data db is not accessible).

When I was logged in as logintest1 I have executed this selects:

SELECT count(*) FROM [master].[sys].[all_views]

SELECT count(*) FROM [master].[sys].[all_columns]

Returns: 401 and 6576

When I was logged in as admin it returns 401 and 7318. This is weird that new user can display all views in entire server.

I have checked that logintest1 is a member only of one server role “public”.

I would like to just create a user that can connect to only one specific db and cannot connect to all others dbs.

Is it possible?

kicaj

_{Thursday, November 15, 2012 9:48 PM}

The reason you can see data in master and tempdb is that the guest user is enabled in these databases, and you cannot change that.

The user does not see all views in the server, only those in the master database.

As Rick said, said you can use a contained database where users only see that database and nothing else to meet your goal. But if you create a user from a login that user needs to be able to access master and tempdb.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

_{Thursday, November 15, 2012 10:15 PM}

The master database shouldn't contain any user data. Only the same stuff that is contained in every SQL Server. So the user isn't learning anything special. Of course there is nothing preventing you from putting sensitive information in master. (Don't do that.)

There are some views that can be selected in master but only show info related to the person using the view. For example SELECT * FROM sys.sysprocesses shows a low privileged user their own process. But it shows a sysadmin all processes.

Some views such as SELECT * FROM sys.dm_exec_connections can't be executed unless you have extra privileges.

And some, like SELECT * FROM sys.databases will show info to users about databases they have no access to, because it would be too slow to enter each of (perhaps) several hundred databases, to see which ones contained a user for the person making the request. This is an intentional design decision so that Management Studio doesn't take 10 minutes to start up.

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

_{Friday, November 16, 2012 6:33 AM}

So it looks that Contained Databases is not an option for me because I want use Windows Authentication on SQL Server. I need login and user that will have access to the specific database.

kicaj

_{Friday, November 16, 2012 8:12 AM}

You can use Windows authentication with contained databases.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se