Post reply

How to get Orphan users information through mail

  • March 8, 2016 at 12:45 pm

    #399822

    Hi All,

    Currently I have received one request from client and asking me to create a job and that needs to capture all orphan users in instance and needs to get mail with all the information.

    Please suggest me to proceed further

  • March 8, 2016 at 1:53 pm

    #1863808

    sp_change_users_login can be used to extract orphaned users.

    sp_send_dbmail can be used to send a report via email.

    Do you have specific issue with those stoed procedures? How can we help you? Show some code and we can suggest based on your issues.

    -- Gianluca Sartori

  • March 8, 2016 at 5:10 pm

    #1863887

    Through Below link we can able to pull all the orphan users in Instance level and I have get the mail with this information.

    http://qa.sqlservercentral.com/blogs/rocks/2011/10/20/finding-orphaned-database-users/

  • March 8, 2016 at 7:03 pm

    #1863899

    New persopn (3/8/2016)

    Through Below link we can able to pull all the orphan users in Instance level and I have get the mail with this information.

    http://qa.sqlservercentral.com/blogs/rocks/2011/10/20/finding-orphaned-database-users/%5B/quote%5D

    I haven't tested it, but looking at that query makes me wonder if Gianluca's sp_change_users_login would be faster. It would certainly be simpler. Either way you go, the the sp_send_dbmail suggestion is the way to go.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 8, 2016 at 7:55 pm

    #1863904

    Re: sp_change_users_login, from the Books Online article:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

    Here is another way to accomplish the same across all databases:

    Loginless In Seattle[/url]

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 8, 2016 at 8:52 pm

    #1863907

    Orlando Colamatteo (3/8/2016)

    Re: sp_change_users_login, from the Books Online article:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

    Here is another way to accomplish the same across all databases:

    Loginless In Seattle[/url]

    I remember when Andreas Wolter first told me about that procedure and I asked him about that very same note. He said that there's nothing to really replace it and he didn't expect it to go away any time soon. I'm thinking that if it is actually removed, I'll try to create a local version of it from the source.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 8, 2016 at 8:56 pm

    #1863908

    Ed Wagner (3/8/2016)

    Orlando Colamatteo (3/8/2016)

    Re: sp_change_users_login, from the Books Online article:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

    Here is another way to accomplish the same across all databases:

    Loginless In Seattle[/url]

    I remember when Andreas Wolter first told me about that procedure and I asked him about that very same note. He said that there's nothing to really replace it and he didn't expect it to go away any time soon. I'm thinking that if it is actually removed, I'll try to create a local version of it from the source.

    I did something similar using the code in the article I linked to.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 9, 2016 at 5:18 am

    #1863952

    Orlando Colamatteo (3/8/2016)

    Ed Wagner (3/8/2016)

    Orlando Colamatteo (3/8/2016)

    Re: sp_change_users_login, from the Books Online article:

    This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.

    Here is another way to accomplish the same across all databases:

    Loginless In Seattle[/url]

    I remember when Andreas Wolter first told me about that procedure and I asked him about that very same note. He said that there's nothing to really replace it and he didn't expect it to go away any time soon. I'm thinking that if it is actually removed, I'll try to create a local version of it from the source.

    I did something similar using the code in the article I linked to.

    I must admit that I didn't check it out the first time. Now I see it's an article you wrote, so I'll have to read it. Thanks.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 9, 2016 at 4:55 pm

    #1864153

    Here you go: http://jonmorisissqlblog.blogspot.com/2016/02/orphaned-users.html

Viewing 9 posts - 1 through 8 (of 8 total)

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