SQL Server Permissions

Here is a handy little script to figure out what permissions have been granted to a user in SQL Server. For example the connect id user for PeopleSoft needs to have select access to the PSACCESSPRFL, PSOPRDEFN and PSSTATUS tables.  The default connect id is ‘people’.

select p.name, p.type_desc, pp.permission_name, pp.state_desc, pp.class_desc, object_name(pp.major_id)
from sys.database_principals p left join sys.database_permissions pp on pp.grantee_principal_id = p.principal_id
where p.name = 'people'