0

I have a user that is attempting to access DatabaseA which he has rights too but he keeps getting the below error (he has access to DatabaseA but doesn't have rights to DatabaseB):

The server principal "Domain\Username" is not able to access the database "DatabaseB" under the current security context.

Because he doesn't have access to DatabaseB, this makes sense at first. Unfortunately, he's not calling DatabaseB in any way.

  • I've watched this come through the server using Profiler down to the statement levels using no filters. I can see his connections and the error come through but there is no indication that DatabaseB is being hit in any way.

  • I've reviewed the stored proc that he is attempting to run. It contains a simple select statement that accesses only tables within DatabaseA.

  • I was able to work around this by giving him access to DatabaseB but this is not a viable long term solution.

  • I've confirmed that the user is not orphaned

  • I've searched Google and there are tons of posts regarding this error but none that describe this scenerio.

If anyone has seen this or has an idea about it, I'd truly appreciate it!

PseudoToad
  • 1,504
  • 1
  • 16
  • 34
  • 2
    Is it possible that one of the tables being queried is not a table at all, but actually a synonym pointing to DatabaseB? `SELECT name, base_object_name FROM sys.synonyms` – Nic Jan 18 '13 at 23:33
  • Hi @Nic...looked there also. We make extensive use of synonyms and it was one of my first thoughts. – PseudoToad Jan 18 '13 at 23:35
  • If you are using AZURE SQL Database: [Check this post](http://stackoverflow.com/questions/6476828/new-user-cannot-login-to-sql-azure/40540430#40540430) – foluis Nov 11 '16 at 02:59
  • If you are using AZURE Sql Database [Check this post](http://stackoverflow.com/questions/6476828/new-user-cannot-login-to-sql-azure/40540430#40540430) – foluis Nov 11 '16 at 03:01

1 Answers1

2

If the connection doesn't specify a database, then the user is connecting to a default database. Check the login information in SQL Server for the default database. The default is probably set to DatabaseB, but the login isn't a user in the database.

You can use Object Browser to view the information. Connect to your SQL Server, navigate the tree to Security > Logins and select the login. Right-click and select properties. Default database is located near the bottom of the form on the General tab.

bobs
  • 21,844
  • 12
  • 67
  • 78
  • @Gizmo - It's the configuration of the login in SQL Server that you should check. Look at my edit. – bobs Jan 18 '13 at 23:39
  • Hi @bobs...that is where I had looked. The default db is definitely Master (right above the default language). Even if this were the case, wouldn't I see this come through the profile as a USE? – PseudoToad Jan 18 '13 at 23:43
  • 1
    @Gizmo - Rats! Have you experimented with changing the default DB, maybe to DatabaseA? – bobs Jan 18 '13 at 23:47
  • Good idea!...I'll give that a shot on Monday. Unfortunately my test subject has left for the day! – PseudoToad Jan 18 '13 at 23:55