Post reply

Script logins and all permissions: no longer supported in SSMS generate script?

  • August 4, 2020 at 4:49 pm

    #3777261

    Okay, maybe I've just gone crazy but I was certain it used to be possible to go to tasks, generate scripts, and then you could script out logins, database users, and all associated permissions?

    However, it looks like this is no longer possible in the newer versions of SSMS?

    Is everyone just using a custom script they run now to generate that output, is there a 3rd party GUI tool you are using to accomplish this?

    I RARELY need to migrate a login from one database to another, or from one prod instance to a dev instance or vice versa.

    Being able to just right click and script out a login and all associated permissions was extremely useful.

  • August 4, 2020 at 6:47 pm

    #3777304

    I don't recall that ever being available in SSMS - but it may have been and just something I never utilized.  With that said - check out https://dbatools.io/ - they have a whole host of cmdlets that can be used.  You can easily export and import logins using these tools - even making sure the SID for SQL logins is transferred.

    I do have custom scripts available - but they are geared specifically for the environments I maintain.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 4, 2020 at 7:09 pm

    #3777309

    i think sp_helprev_login has been the go to solution for years for logins.

    database permissions are stored in the databases themselves, and those are scriptable from SSMS , and if you use ObjectExplorer you can script users all together.

    there is a setting in Tools>>Options>>SQL Server Object Explorer>>Scripting you have to toggle "Script permissions" to get object permissions, i think.

     

    i use a TSQL solution I adapted from Perry Wittle, here's the first link i could find, might not be his "latest" version though.

    https://www.sqlservercentral.com/forums/topic/script-out-dbobject-level-permissions-grantdeny

    2020-08-04_15-08-43SSMS.

    • This reply was modified 4 years, 4 months ago by  Lowell.
    • This reply was modified 4 years, 4 months ago by  Lowell.

    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!

Viewing 3 posts - 1 through 2 (of 2 total)

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