SQL Server: Find out which users have write access to which tables?
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
![Peter Howe](https://i.stack.imgur.com/OqMvU.jpg?s=256&g=1)
Peter Howe
Development team leader, working in Covent Garden, London. PHP, JavaScript, processes, svn
Updated on July 07, 2022Comments
-
Peter Howe almost 2 years
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.