Post reply

SQL 2005 Get Roles, Schemas & Object Permissions

  • November 28, 2008 at 12:02 am

    #383479

    Comments posted to this topic are about the item SQL 2005 Get Roles, Schemas & Object Permissions

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • November 28, 2008 at 7:15 am

    #904772

    Hey Brandie, thanks for posting the code.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • November 28, 2008 at 8:15 am

    #904818

    Thanks for the code

    I think you missed the @DBName declaration at the top

    Declare @sqlstmt varchar(5000);

    declare @DBName sysname;

    set @DBName = DB_NAME()

    set @sqlstmt = 'use ['+@DBName+']' + char(10)

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005

  • December 3, 2008 at 4:54 am

    #906753

    DOH! Good catch, Jerry. I was so busy trying to make sure I didn't post code w/o permission that I must have accidently dropped that declare statement.

    My bad. :blush:

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • October 7, 2011 at 10:29 am

    #1391860

    brandie, a bit late perhaps but as you ask in the code column level permissions are in the minor_id column in sys.database_permissions

    If minor_id = 0 its not a column level permissions else it can be derived thus

    col_name(sys.database_permissions.major_Id, sys.database_permissions.minor_id)

    ---------------------------------------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

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