How do I script out table permissions in SSMS?

  • I would like to script out all the permissions that a database user has and then use that script to alter or create a new user. I remember that in 2000 you could view permissions of a user and script it out, then all I would need to do is change the username and run the script.

    Any help would be great.

    Thanks

    Aaron

    Aaron
    MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
    https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard

  • It's by object. You can right-click on the database and choose Tasks | Generate Scripts. When you get to the Script Options screen, you'll need to set Script Object-Level Permissions to True.

    K. Brian Kelley
    @kbriankelley

  • Thanks, I have tried that but it is not very clean. I wonder if they have found a better solution in 2008.

    Aaron

    Aaron
    MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
    https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard

  • No, they haven't. The scripting options are pretty much the same.

    K. Brian Kelley
    @kbriankelley

  • K. Brian Kelley (8/14/2008)


    It's by object. You can right-click on the database and choose Tasks | Generate Scripts. When you get to the Script Options screen, you'll need to set Script Object-Level Permissions to True.

    Unfortunately, this does not get everything either, in part, because not all of the security is bound to objects. For instance, none of the Service Broker items are included in this. Also, I think that some of the Role/Membership stuff is left out unless you are at SP2.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • True. I think I'm just going to write a script to extract the info. A royal pain, but it seems necessary.

    K. Brian Kelley
    @kbriankelley

  • i would be in your debt.

    Aaron

    Aaron
    MCITP: Database Administrator, MCITP: Server Administrator, MCTS: Windows Server 2008 Active Directory – Configuration, MCTS: Windows Server 2008 Network Infrastructure – Configuration, MCTS: Windows Server 2008 Application Platform – Configuration, MCTS: SQL Server 2005, MCTS: Microsoft Exchange Server 2007 – Configuration, MCSE 2003, MCSA 2003, MCDBA, Security+, CCNA
    https://www.mcpvirtualbusinesscard.com/VBCServer/AaronChristenson/interactivecard

Viewing 7 posts - 1 through 6 (of 6 total)

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