SQL 2005 Get Roles, Schemas & Object Permissions

Question

SQL 2005 Get Roles, Schemas & Object Permissions

Brandie Tarvin

SSC Guru

Points: 172994
More actions
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.

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

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 2

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)

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Brandie Tarvin SSC Guru Points: 172994 More actions · Answer 3

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.

george sibbald SSC Guru Points: 104210 More actions · Answer 4

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)

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