Post reply

Impersonate accounts

  • April 5, 2012 at 5:14 am

    #254450

    Hi,

    I've a stored procedure that checks a few vital operational functions. One thing I do is to check that 2 domain accounts can access a database across a linked server, at 15 minute intervals. However, last night in our test environment, one of the 2 domain accounts got locked out in AD, which caused some jobs to fail. Upon checking my stored procedure logging table, I was surprised to see that it had merrily reported success throughout the night. I presumed that there must be something wrong with my code and that it wasn't impersonating the Windows account properly. Howerver, following some testing, this method of impersonating accounts only checks that the account exists and has the appropriate level of permissions in SQL Server.

    My test was:

    Create an AD account called 'testsqlacc'

    Create a login for 'testsqlacc' with dbo on 'mytestdb'

    Create the following stored proc:

    CREATE PROCEDURE testsql

    AS

    BEGIN

    EXECUTE AS LOGIN = 'MYDOMAIN\testsqlacc'

    SELECT TOP 5 * FROM mytestdb.dbo.mytesttable

    SELECT suser_sname()

    GO

    I execute the stored procedure and I get 5 rows back

    I disable the 'testsqlacc' in AD and get 5 rows back when exec'ing stored proc

    I delete the 'testsqlacc' in AD and get 5 rows back when exec'ing stored proc

    Remove dbo from the 'testsqlacc' login and exec the stored procedure. I then get:

    Msg 15404, Level 16, State 11, Procedure testsql, Line 4

    Could not obtain information about Windows NT group/user 'MYDOMAIN\testsqlacc', error code 0x534

    Ultimately, I want a way of not only validating that the SQL Server login is working and has appropriate permissions, but that it's also not locked/disabled/deleted, etc. in AD.

    Thanks for looking

  • April 5, 2012 at 9:02 am

    #1469597

    anyone?

  • April 5, 2012 at 9:51 am

    #1469656

    Did you see this thread?

    http://www.sqlservercentral.com/Forums/Topic651094-338-1.aspx#bm651837

  • April 5, 2012 at 10:03 am

    #1469667

    adb2303 (4/5/2012)

    I execute the stored procedure and I get 5 rows back

    great...working as expected

    I disable the 'testsqlacc' in AD and get 5 rows back when exec'ing stored proc

    disabling a user in AD only prevents the LOGIN from connecting, the usage of the login/user in SQL is still valid, and still has permissions, so when you EXECUTE AS it will still work.

    if you disable the login in SQL server, access would be blocked.

    you could change the proc to use EXECUTE AS USER='domain\login', and it would work as well.

    i actually have a few database USERS without login i created for exactly this scenarion: getting data with elevated permissions, but don't want real users to have access to it.

    I delete the 'testsqlacc' in AD and get 5 rows back when exec'ing stored proc

    same reason as above...it's the database user that has the permissions, which is differnet from the login.

    if you tried to login as that user, it would fail.

    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!

  • April 5, 2012 at 10:57 am

    #1469705

    I think that your best bet for checking the AD account status would be to setup an ADSI linked server and query the account there. That way you'll be able to confirm that all is hunky dory with it there (although that obviously doesn't cover checking that the other aspects are fine).

    Check around for ADSI stuff (ie http://www.sqlservercentral.com/scripts/LDAP/61519/[/url]


    Shamless self promotion - read my blog http://sirsql.net

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

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