New login permissions - too broad

Hi,

I created a new sql server login, but didn't assign it any permissions in any databases.

When I login with this new login, it logs into the master database, and is able to select tables from the system databases, such as master, msdb.

This seems very wrong to me. How can I turn these default permissions off for new logins? I thought it might have something to do with the guest account, but not sure how to best handle this.

Thanks

This is normal, default behavior, and it is considered a best practice to leave the behavior the way it is. If don't like it, you can always manually deny a specific user permissions to SELECT on specific tables in the master and msdb databases.

|||

Ok.

Do you have any links where I can read up more on this? I'd like to find out exactly what permissions new logins have by default in these system databases.

I know that you can't disable the guest account in master or tempdb, as they are needed there.

Thanks

|||

That is because, by default VIEW DEFINITION has been granted to public. If you want this and all subsequent logins to not be able to view metadata in the system databases, then execute the following:

REVOKE VIEW DEFINITION FROM public

GO

That will prevent them from seeing any metadata i.e. the databases will "appear" to be blank to them. They will still be able to do things like SELECT * FROM sys.databases. So, to prevent them from selecting from any of the DMVs, execute the following:

REVOKE SELECT ON DATABASE::master FROM public