I have sa permissions on server yet I'm unable to grant myself IMPERSONATE in any server database.
--i tried
GRANT IMPERSONATE ANY LOGIN TO [domain\myself];
GO
--Getting Error:
Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
What do I need to do so that I can impersonate another user like this:
SETUSER 'xxxx\theOtherUser'
USE [SCDatabase]
select top 100 *
from [SCDatabase].[dbo].[Subscription]
--getting error (and the other user definitely exists):
Msg 15157, Level 16, State 1, Line 1
Setuser failed because of one of the following reasons: the database principal 'xxxx\theOtherUser' does not exist, its corresponding server principal does not have server access, this type of database principal cannot be impersonated, or you do not have permission.
--Quote me
April 19, 2020 at 7:50 am
If you are sysadmin you can't give permissions to yourself - neither do you need it on this case so remove that from your script.
regarding setuser - do not use it as it is deprecated - use "execute as" instead. see here
and make sure that after each execute as and once you are finished with the code to execute as the other user/login that you execute a "revert" so permissions revert to your own user - if not then next time you try and do a "execute as" you will get an permissions error
so on your case you most likely need
execute as login = 'xxxx\theOtherUser'
use db
select ...
revert
April 20, 2020 at 11:38 pm
Hi frederico_fonseca, Thanks for your time.
I get same error with EXECUTE. See attached image.
theOtherUser is a Login on the Server (with only 'public' security privilege) and a User on the Database where I have given them Select & View definition / Grantor dbo . It is verified that they are able to select from an existing view on that database and that they exist so the part of the error having to do with them not existing can not apply.
myself is a Login on the Server (with all ALL security privileges: bulkadmin down to sysadmin) , with 'Connect SQL / Grantor sa'. I see a securable called 'Impersonate Any Login' and that is not checked.
It is for this reason I am thinking that the issue is that I do not have Impersonate permissions. The other possibility is that 'this type of principal cannot be impersonated', but what kind of principal can't be impersonated? This principal is a login that is created in IDWEB and multiple Active Directory Windows alias can be mapped to this login.
--Quote me
April 21, 2020 at 5:32 am
try the following code - without changes other than the login name and give us the output
if (original_login() <> suser_name())
begin
print 'revert'
revert
end
go
print 'User ' + suser_name() + case when IS_SRVROLEMEMBER ('sysadmin') = 1 then ' Is sysadmin' else ' not a admin' end
execute as login = 'xxxxx' --- REPLACE WITH CORRECT LOGIN ---
print 'Original login = ' + original_login() + ', current user = ' + suser_name()
use SCFeed
select top 100 *
from SCFeed.SCFeed.Subscription
if (original_login() <> suser_name())
begin
print 'revert'
revert
end
April 21, 2020 at 6:36 am
Hi Frederico_Fonseca,
This is what I got:
User DOMAIN\myself Is sysadmin
Msg 15406, Level 16, State 1, Line 10
Cannot execute as the server principal because the principal "DOMAIN\theOtherUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Completion time: 2020-04-20T23:27:16.0486208-07:00
Attached screenshot.
--Quote me
April 21, 2020 at 11:37 am
can you give the output of these please.
select containment_desc
from sys.databases
where name = db_name()
select type_desc
, is_disabled
, default_database_name
from sys.server_principals
where name = 'xxxx' -- replace with login name
select serverproperty('EngineEdition')
April 21, 2020 at 5:25 pm
another one - just weeding out options as not quite sure of what the issue is.
EXEC xp_logininfo 'xxx'
and another option - it is possible that the user is setup on Active Directory with option of User can not be impersonated - you would need to check that with your windows admins
April 23, 2020 at 7:08 pm
Hi Frederico,
Attached snapshots of results for both logins
I will create a single user (instead of using a security group name mapped to multiple aliases) and see if impersonate works against that.
--Quote me
April 30, 2020 at 6:15 am
I am able to impersonate a single user (as opposed to a Security Group mapped to multiple users in AD).
IT SEEMS TO BE WORKING ONLY AS LONG AS I IMPERSONATE A SINGLE USER AND RUN THE QUERY ONLY ONCE PER CONNECTION
Second time I run SET USER in same session I get the error again....
Still testing.
--Quote me
as per my previous examples you need to REVERT the execute once you are finished with the impersonation.
and you never told us that your login was a AD group - had you said that initially I would have told you immediately that you would not be able to impersonate it.
And I missed it on your previous post with the output of xp_logininfo
April 30, 2020 at 3:58 pm
If I had known the reason for the problem I wouldn't have posted the question. Isn't that the point of posting Frederico? Now I understand that impersonating a login mapped to users in AD is the cause of the error.
However I still get the error if I tried to run the impersonate in the same session twice, and that also doesn't make sense.
--Quote me
May 1, 2020 at 6:12 am
anyway, thanks Frederico.
Are you saying that it's expected to be able to impersonate only once per connection because once I have impersonated I am not longer operating as sysadmin? Also, is REVERT alone all I have to do to revert to SA? as in one liner 'REVERT'?
--Quote me
May 1, 2020 at 6:50 am
yes until you do the REVERT you are not a sysadmin as you are the OTHER user
have a look at the examples I gave here - it is very clear what you need to do
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply