Search code examples
sql-serverdatabase-permissions

SQL Server: Find out which users have write access to which tables?

In SQL Server 7.0, I need to find out which users have write access to which tables in a particular database.

I know that I can do this in Enterprise Manager by going through each table in the database and looking at the access granted to those tables - but there are a few hundred tables in the database. As I'm only concerned with a handful of users, I'd rather be approaching it from the users.

Is there a query I can run on system tables to achieve this? Is there another approach via Enterprise Manager?

What I'm after is something that basically says:

UserOne has write access to Table1, Table2 and Table3
UserTwo has write access to Table2 and Table3
etc.
Solution 
  • SELECT u.name, o.name
FROM syspermissions p, sysobjects o, sysusers u
WHERE p.id = o.id
AND u.uid = p.grantee
AND u.name IN ('UserOne', 'UserTwo', 'UserThree')
AND o.xtype = 'U'
AND p.actadd = 27

    The magic 27 is built from 1 (SELECT) + 2 (UPDATE) + 8 (INSERT) + 16 (DELETE)

    Thanks to Matt Lacey (and Google!) for setting me on the right course: http://blog.mrlacey.co.uk/2007/06/checking-database-permissions-in-sql.html