How to find permissions granted on user in a database?

Run the following SQL to find permissions granted on users on the database

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id
inner join sysusers objectowner on o.uid = objectowner.uid
WHERE u.name = 'username'
order by o.name, u.name

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s