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?
3
-
What would you do if I had clicked there? It shows in management studio, and the buttons are very close together... it would be so easy to do this by accident all the time. As long as access is denied, what's the harm?– Joel CoelSep 30, 2013 at 17:00
-
It's more of a security thing to make sure someone doesn't even try to access anything. What you can't see, you can't even attempt to break into.– Scott SalyerOct 1, 2013 at 15:20
-
But I can see it. I'd discourage this: it doesn't really add any security. Trying passwords over and over is one thing, but this is for an already verified account. Either the permissions are set up correctly or they aren't.– Joel CoelOct 1, 2013 at 15:55
Add a comment
|
1 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.