April 7, 2016 at 7:57 am
Well, this is somewhat frustrating.
Our corporate IT department recently did away with a domain (we'll call it ABC) and changed everything over to another domain (we'll call it XYZ). On the surface of it, the login moves was supposed to be somewhat transparent. Meaning we were told the "new" logins would just be the old logins "renamed" to the new domain and retaining the old SIDS.
So technically it worked, but we also had to technically update some server level logins to XYZ\MyLogin from ABC\MyLogin. But in the database, the login is still ABC\MyLogin, it's just mapped to XYZ\MyLogin.
Follow me so far?
For our server upgrade we have completely new logins with completely new SIDS. So the app account is now XYZ\TotallyApp instead of XYZ\MyLogin (these account names are all scrubbed, BTW).
Way back in the day, ABC\MyLogin granted IMPERSONATE on a SQL Acct (MySQLLogin) before the domain change. Now in the new environment I can't use T-SQL to REVOKE IMPERSONATE off of MySQLLogin. Oddly enough, the statement doesn't error out. SSMS always says "Command Completed Successfully". But when I try DROP USER for ABC\MyLogin, the command fails because ABC\MyLogin has granted permissions to another account. XYZ\MyLogin doesn't exist on the new server and I can't add ABC\MyLogin to it because ABC no longer exists in Active Directory.
The only way around this (that I've found so far) is to go into the properties (Database -> Security -> Logins) of MySQLLogin to unclick the IMPERSONATE under Securables, then to do the same under ABC\MyLogin. But I'd rather have a T-SQL script do this so I can add it to our final post-upgrade move script and not have to make this a separate item on the checklist.
Has anyone run into this problem before? Is there a T-SQL way of doing this (other than the standard REVOKE IMPERSONATE code which isn't working)? Any other helpful thoughts or suggestions on this matter?
April 7, 2016 at 12:40 pm
After you do your clicking in the properties windows, can't you just generate the SQL script for what you did using the button at the top instead of clicking OK?
April 8, 2016 at 7:00 am
Recurs1on (4/7/2016)
After you do your clicking in the properties windows, can't you just generate the SQL script for what you did using the button at the top instead of clicking OK?
Well that IS interesting. When I scripted it out that way, it added an extra switch that I haven't seen documented anywhere.
Instead of:
use [DB]
GO
REVOKE IMPERSONATE ON USER::[ABC\MyLogin] TO [MySQLLogin]
GO
REVOKE IMPERSONATE ON USER::[MySQLLogin] TO [ABC\MyLogin]
GO
It became
use [DB]
GO
REVOKE IMPERSONATE ON USER::[ABC\MyLogin] TO [MySQLLogin] AS [ABC\MyLogin]
GO
REVOKE IMPERSONATE ON USER::[MySQLLogin] TO [ABC\MyLogin] AS [MySQLLogin]
GO
And the T-SQL appears to have worked. WHOO HOO!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply