March 8, 2016 at 12:45 pm
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
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
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://www.sqlservercentral.com/blogs/rocks/2011/10/20/finding-orphaned-database-users/
March 8, 2016 at 7:03 pm
New persopn (3/8/2016)
http://www.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.
March 8, 2016 at 7:55 pm
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:
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 8, 2016 at 8:52 pm
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:
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.
March 8, 2016 at 8:56 pm
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:
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
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:
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.
March 9, 2016 at 4:55 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply