none
How to script out Login Permissions in SQL Server RRS feed

  • Question

  • HI, 

    We know using Microsoft provided SP SP_Help_RevLogin, we can script out logins but it doesn't script out permissions for those logins. Using DBATools we can easily script out both Logins and permissions but we can not use DBATools due to some constraints at your end. Is there any other way or free tool which can script out logins for us ?

     Appreciate your insightful response. Thank you. 

    Thursday, May 21, 2020 5:05 AM

Answers

All replies

  • Thursday, May 21, 2020 7:26 AM
  • Hi friend,

    Please check below articles if help:

    Move or copy SQL Logins by assigning roles and permissions

    It’s 2016, so why are you still using sp_help_revlogin?

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 21, 2020 10:01 AM
  • Why exactly are you trying to script out logins?  What exactly are you trying to accomplish?  What version of SQL Server are you using?

    Thursday, May 21, 2020 12:19 PM
  • Hi Tom. 

    We are on SQL Server 2019. In order to support HA and Disaster recovery, we are setting up Alwayson for Production databases.  As AG supports database level availability, we are moving all the server level objects to other servers including SQL Server Agents, Jobs, Operators,  logins and assigned permissions, etc. 

    This is why we need to script out Logins and permission and keep the script ready so even-DBA can take care of corresponding changes made on one server - If a new login is created on a server, sync login script when executed should create same login on another server(s).

    Stay blessed and safe in these difficultly times !!

    Thursday, May 21, 2020 2:07 PM
  • Hello Friend, 

    Hope you are doing well....

    I know DBATool is awesome and in fact we could accomplish almost all the manual tasks by calling a single command in Powershell.  But we are not sure we can package DBAtool in our tool. 

    We are developing a tool to automate certain implementation in SQL Server. We have checked that by importing DBAtools from Powershell, we can run command (available only in dbatools) from Power shell without any issues.

    Question is, can we integrate dbatools in out tool that we are going to sell to our customers

    Appreciate your insightful response. Thank you.

    Thursday, May 21, 2020 2:14 PM
  • We are on SQL Server 2019. In order to support HA and Disaster recovery, we are setting up Alwayson for Production databases.  As AG supports database level availability, we are moving all the server level objects to other servers including SQL Server Agents, Jobs, Operators,  logins and assigned permissions, etc. 

    I'm a little curious about the permissions thing here. In this context that would only be server-level permissions, which should not be that many. Particularly, I would not expect plain application users to have any server-level permissions at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 21, 2020 9:26 PM
  • Hi friend,

    >>>Question is, can we integrate dbatools in out tool that we are going to sell to our customers ?
    It is best to consult with experts in this area.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 22, 2020 1:58 AM
  • Hi Anil,

    Thank you for your post. Have you solved this problem?  In order to close this thread, please kindly mark useful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 26, 2020 8:33 AM
  • Hello Chris, 

    I am still stuck - please wait for my confirmation. 

    Above scripts were creating logins, we need to sync login permission which means we need to drop and recreate logins whoes permissions have been changed. Create Login script works perfect for Domain users but for SQL Server, password is getting truncated thus failing the execution. 

    Will keep you posted. 

    Wednesday, May 27, 2020 4:50 AM
  • Hello Anil,

    Have a nice day.
    I will wait for your confirmation.

    Best Regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 27, 2020 5:39 AM
  • Question is, can we integrate dbatools in out tool that we are going to sell to our customers

    I guess you would need to read their license. Or even better, get in touch with them to hear what they think about it.

    But it goes without saying that you can only charge your customers for whatever value you may add to it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 27, 2020 9:41 PM
  • Question is, can we integrate dbatools in out tool that we are going to sell to our customers

    I guess you would need to read their license. Or even better, get in touch with them to hear what they think about it.

    But it goes without saying that you can only charge your customers for whatever value you may add to it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Hi Erland,

    Thank you for your response.....

    We checked licensing terms for DBAtools - It comes under MIT license and it can be distributed freely. We intend to use DBAtools for syncing logins and permissions and our tool would set up HADR on customer site.

    https://dbatools.io/mit/

    https://choosealicense.com/licenses/mit/

    https://choosealicense.com/licenses/gpl-3.0/

    Friday, May 29, 2020 10:23 AM