SQL users and permissions

[joemal]

How can I query all the users and their permissions on my databases?

 

[hmckillop]

Below is some code I posted before for getting permissions for users for stored procs. You can use this as basis for getting all the users from the system and for what objects - sorry I dont have time to change it.

Declare @StoredProcName Varchar(20)
Declare @User  varchar(20)

Set @StoredProcName = 'sb_spWeeklyReport'
set @User='webuser3'

SELECT  syspermissions.grantee
From 
    syspermissions
Inner join 
    sysobjects ON sysobjects.id= syspermissions.id
INNER JOIN
     syscolumns ON syscolumns.[ID] = sysobjects.[ID]
INNER JOIN 
    Sysusers ON Sysusers.uid = syspermissions.grantee

WHERE
     OBJECTPROPERTY( sysobjects.[ID], 'IsProcedure') = 1
AND 
    sysobjects.name = @StoredProcName
AND 
    Sysusers.name=@User
    
if @@rowcount >0 
    Print 'Got permisons'
Else
    print'not got persions'

"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.