Home / server / Questions / 542727
Accepted
Scott Salyer
Asked: 2013-10-01 08:17:47 +0800 CST

SQL Server Access Logs

  • 772

Is it possible to show some type of access log where a valid user (who had access to a specific database [or multiple ones]) tried to access a database they didn't have access to? For example, if I login with a SQL Server account to a database server via SMS and then try to expand a database that my account doesn't have access to, I get a "The database [DBName] is not accessible" error. Is there any type of log to show the user that attempted this? On this same note, is it possible to limit the list of databases shown in SMS to only the one the user has access to and not even show the other ones?

sql-server-2012

1 Answers

  1. Best Answer

    I couldn't find any type of access log options to show attempts at getting to a database a user didn't have access to, but I was able to restrict their access by doing the following on Master:

    DENY VIEW ANY DATABASE TO LOGIN [UserName]

    This prevents the user from seeing any database in SMS. The next thing to change was the ownership of any database(s) I wanted the user to be able to see by going to properties and setting the owner. At that point a logged in user can only see the databases they own and nothing else which is a form of implicit security and satisfies my need.

    • 0