0

What I'd like to do is evaluate a SQL expression like the following:

IF HAS_DBACCESS('ExampleDB') = 1
BEGIN
    USE ExampleDB
    --Other stuff
END

As per this query, it should only perform actions relating to ExampleDB should the user executing this query have access to ExampleDB.

When I run this query as a user without access to this DB, I get the following error message:

The server principal "MY_USERNAME" is not able to access the database "ExampleDB" under the current security context.

This is odd to me. I do not have access to ExampleDB. Therefore, HAS_DBACCESS('ExampleDB') evaluates to 0. I've confirmed this by selecting its value. In fact, if I replace the USE ExampleDB with anything other than a USE statement (for example, SELECT 'Test'), it doesn't evaluate, which is correct behaviour.

For some reason, it's trying to evaluate access for USE ExampleDB before even checking the condition. Is this expected behaviour? And if so, is there any way around this?

Jon Warren
  • 857
  • 6
  • 18
  • I might be pulling this incorrectly from the depths of my brain, however, I *think* a `USE` statement is derived regardless of where it is in the batch, even a part that might not run. You'll probably need to use Dynamic SQL to pull off whatever it is you're trying to do. – Thom A Jul 20 '18 at 16:53
  • Possible duplicate of [USE DB that may not exist](https://stackoverflow.com/questions/14200166/use-db-that-may-not-exist) – hatchet - done with SOverflow Jul 20 '18 at 16:59
  • The duplicate linked above is not an exact duplicate but the problem and answer is the same. It boils down to the fact that the USE statement is executed during compile time as well as execution time. – hatchet - done with SOverflow Jul 20 '18 at 17:00

1 Answers1

0

I would not do it this way because, yes, it is going to throw that error. Try doing something like this and it works, but it won't work with your USE statement:

IF HAS_DBACCESS('blahhhhhhh') = 1
begin
print 'hello'
end
else 
begin
print 'bad'
end

An option would be to fully qualify stuff with the db name in your branch: 

IF HAS_DBACCESS('ExampleDB') = 1
BEGIN
    SELECT * FROM ExampleDB.dbo.Your_table
END
ELSE 
BEGIN 
    SELECT * FROM otherDB.dbo.Your_table
END
dfundako
  • 8,022
  • 3
  • 18
  • 34
  • The reason I'm not fully qualifying is because I specifically wanted to grab the object definition of a stored procedure or view with OBJECT_DEFINITION(my_obj). I wasn't able to do OBJECT_DEFINITION(ExampleDB.dbo.my_obj) , is there any other way of doing that? I understand that this is a separate question from what I was asking, but I'd still like to know if there is a solution to either – Jon Warren Jul 20 '18 at 17:14
  • 2
    @JonWarren As the link you were given states, USE is evaluated at compile time. To continue down this path you must use dynamic sql. If you're only interested in procedures and views you can retrieve the same information from [sys.sql_modules](https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-modules-transact-sql?view=sql-server-2017) – SMor Jul 20 '18 at 19:26