Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL 2005 Get Roles, Schemas & Object Permissions Expand / Collapse
Author
Message
Posted Friday, November 28, 2008 12:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 7,053, Visits: 6,214
Comments posted to this topic are about the item SQL 2005 Get Roles, Schemas & Object Permissions

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #610114
Posted Friday, November 28, 2008 7:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:31 AM
Points: 10,910, Visits: 12,548
Hey Brandie, thanks for posting the code.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #610334
Posted Friday, November 28, 2008 8:15 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:45 PM
Points: 770, Visits: 1,178
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
Post #610387
Posted Wednesday, December 03, 2008 4:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:55 AM
Points: 7,053, Visits: 6,214
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.


Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #612705
Posted Friday, October 07, 2011 10:29 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:25 PM
Points: 5,849, Visits: 12,586
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)


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

Post #1187255
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse