Need help with DB user permissions error

Question

Need help with DB user permissions error

webrunner

SSC-Dedicated

Points: 31866
More actions
December 8, 2014 at 10:38 am

#295668

Hello experts,
I am dealing with an odd error. Probably I'm just not understanding the permissions rules involved.
The server is SQL 2012 Enterprise Edition, but MyDatabase is SQL 2008 compatibility mode.
1. A developer reported a query failing, so I began to test using EXECUTE AS.
2. I saw this error message:
Msg 916, Level 14, State 1, Line 4
The server principal "mylogin" is not able to access the database "MyDatabase" under the current security context.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.MyTable'.
3. So I added the login "mylogin" to "MyDatabase" (no db roles) and then granted SELECT on dbo.MyTable to the user "mylogin" in "MyDatabase".
4. Yet I still keep getting the error in #2.
5. I also see a duplicate listing of the SELECT permissions in the database, but from what I have read it is a "red herring" and not related to the error. Also, I see the "grantor" is "dbo" for one securable SELECT permission, grantor is blank for the other SELECT permission. Not sure if that is relevant.
6. I ran this query to see what database the login "mylogin" can see:
EXECUTE AS LOGIN = N'mylogin';
GO
SELECT name FROM sys.databases WHERE HAS_DBACCESS(name) = 1;
GO
REVERT;
and "MyDatabase" is listed.
7. Just to check, I added "mylogin" to the db_datareader role for "MyDatabase" and I *still* get the error in #2.
8. I removed the db_datareader role.
Does anyone know what is going on, what I am missing here?
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what database are you STARTing in?

select * from dbo.MyTable would fail if you start in master. there's no MyTable in master, regardless of your permissions in MyDatabase

a three part query for testing should work:

EXECUTE AS LOGIN-'juan'

SELECT suser_name() --am wearing my juan mask?

SELECT * FROM MyDatabase.dbo.MyTable

next up, maybe the MyTable is actualy a VIEW? a view that references otehr databases, or a calcualted column which references another database might give you a similar error message.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webrunner SSC-Dedicated Points: 31866 More actions · Answer 2

Thanks for your reply!

First, some additional info.

1. It is definitely a table, not a view.

2. I did get back the user 'mylogin' with the suser_name() query that you suggested.

3. Curiously, though, your suggestion made me realize I used 'user' instead of 'login' - with 'login', this works:

execute as login='mylogin'

use [MyDatabase]

go

select * from MyTable

go

In other words, when I use the 'login' parameter for EXECUTE AS, it works.

Do you know why this query would fail using execute as user? The user and login names are the same, but maybe there is another difference I'm not taking into account.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think it's a scope issue.

it looks like technically there are two [myuser] users, in two different databases.

they only get JOINED to be the same person when you use a login, otherwise they are technically seperate entities.

so your example like this fails, because DB1.myuser does nto have access:

use master;

go

execute as user='mylogin'

use [MyDatabase]

go

select * from MyTable

while a correctly scoped login test would work, i think:

use [MyDatabase]

go

execute as user='mylogin'

select * from MyTable

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webrunner SSC-Dedicated Points: 31866 More actions · Answer 4

Lowell (12/9/2014)
i think it's a scope issue.
it looks like technically there are two [myuser] users, in two different databases.
they only get JOINED to be the same person when you use a login, otherwise they are technically seperate entities.
so your example like this fails, because DB1.myuser does nto have access:
use master;
go
execute as user='mylogin'
use [MyDatabase]
go
select * from MyTable
while a correctly scoped login test would work, i think:
use [MyDatabase]
go
execute as user='mylogin'
select * from MyTable

Yes, that worked!

Thanks so much!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html