Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

List all tables and columns that you have update and select permissions for on SQL Server 2005 and higher

From Wiki

Jump to: navigation, search

SQL Server 2005 introduced the has_perms_by_name function. If you quickly want a list of all the column and tables in the database that you have SELECT and UPDATE permissions for then you can use the query below. The query will list the table name, the column name, a 1 if you have UPDATE/SELECT permissions a 0 otherwise

  1. SELECT object_name(c.object_id) as TableName
  2.     ,name AS ColumnName,
  3.     has_perms_by_name(object_name(c.object_id) , 'OBJECT', 'SELECT', name, 'COLUMN')
  4.     AS SelectPermissions,
  5.     has_perms_by_name(object_name(c.object_id) , 'OBJECT', 'UPDATE', name, 'COLUMN')
  6.     AS UpdatePermissions
  7.      FROM sys.columns AS c
  8.     WHERE OBJECTPROPERTY(c.object_id,'IsMSShipped') =0
  9.     ORDER BY TableName,ColumnName
  10. GO

Here is some sample output

TableName	ColumnName		SelectPermissions	UpdatePermissions
SomeTable	account 			1				1
SomeTable	credentials 			1				1
SomeTable	enable				1				1
SomeTable	flag				1				1
SomeTable	last_mod			1				1
SomeTable	last_user			1				1
SomeTable	port				1				1
SomeTable	server				1				1
SomeTable	sometype			1				1
SomeTable	default_credentials		1				1
SomeTable	user				1				1

Contributed by: --SQLDenis 16:31, 26 May 2010 (GMT)

Part of SQL Server Admin Hacks

776 Rating: 2.3/5 (41 votes cast)