Determine if a user has write permissions on a table 1

[bitwise]

How can I, with an SQL statement determine if a user has write permissions on a table? I have searched through MSDN and all the SQL sys tables and have had many attempts but cannot seem to get it exactly. Does anyone have any thoughts?

Thanks,
Paul

 

[tlbroadbent]

You can use the Permissions function to determine if the Current User has update permissions.

Example: Determine if current user can update Locations table

Declare @upd int
Set @upd=0
Set @upd=
-- User has update ALL permissions
(permissions(object_id('location'))&0x4) +
-- User has insert permission
(permissions(object_id('location'))&0x8) +
-- User has delete permissions
(permissions(object_id('location'))&0x10) +
-- User has update ANY permissions
(permissions(object_id('location'))&0x2000)

Print @upd

If @upd>0
Print 'User can update the table'

-------------------------------

Other tools available are sp_helprotect, sp_helprolemember, INFORMATION_SCHEMA.TABLE_PRIVILEGES (view) as well as the sysprotects and syspermissions tables. These are documented in SQL BOL. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[bitwise]

Thanks for the information - very helpful.

-bitwise