locked
check table permission RRS feed

  • Question

  • Hi All

    Are there script so that I can get all the permission of the tables in a database?

    • Moved by Tom Phillips Wednesday, June 6, 2012 9:31 PM Security Question (From:SQL Server Database Engine)
    Wednesday, June 6, 2012 9:51 AM

Answers

  • IF PERMISSIONS()&2=2
       CREATE TABLE test_table (col1 INT)
    ELSE
       PRINT 'ERROR: The current user cannot create a table.'
    --------
    This example determines whether the current user can
     grant the INSERT permission on the authors table to another user.

    IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
       PRINT 'INSERT on authors is grantable.'
    ELSE
       PRINT 'You may not GRANT INSERT permissions on authors.'

    ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_6f78.asp> How can I retrieve a list of objects and permissions for a specified role?
    ------------------------------------------------------------------------------

    SELECT HAS_DBACCESS ( 'database_name' )
    SELECT HAS_PERMS_BY_NAME('Adventureworks2008', 'DATABASE', 'BACKUP DATABASE');

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Iric Wen Friday, June 8, 2012 7:45 AM
    • Marked as answer by Iric Wen Monday, June 18, 2012 1:58 AM
    Wednesday, June 6, 2012 9:53 AM
  • Hi,

    this may help

    SELECT DB_NAME() AS database_name
    ,OBJECT_NAME(a.major_id) AS table_name
    ,USER_NAME(a.grantee_principal_id) AS grantee_principal
    ,USER_NAME(a.grantor_principal_id) AS grantor_principal
    ,a.permission_name
    ,a.state_desc
    FROM sys.database_permissions a, sys.tables b
    WHERE a.major_id=b.object_id
    ORDER BY OBJECT_NAME(a.major_id)

    • Marked as answer by Iric Wen Monday, June 18, 2012 1:58 AM
    Thursday, June 7, 2012 7:50 AM

All replies

  • IF PERMISSIONS()&2=2
       CREATE TABLE test_table (col1 INT)
    ELSE
       PRINT 'ERROR: The current user cannot create a table.'
    --------
    This example determines whether the current user can
     grant the INSERT permission on the authors table to another user.

    IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
       PRINT 'INSERT on authors is grantable.'
    ELSE
       PRINT 'You may not GRANT INSERT permissions on authors.'

    ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_6f78.asp> How can I retrieve a list of objects and permissions for a specified role?
    ------------------------------------------------------------------------------

    SELECT HAS_DBACCESS ( 'database_name' )
    SELECT HAS_PERMS_BY_NAME('Adventureworks2008', 'DATABASE', 'BACKUP DATABASE');

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    • Proposed as answer by Iric Wen Friday, June 8, 2012 7:45 AM
    • Marked as answer by Iric Wen Monday, June 18, 2012 1:58 AM
    Wednesday, June 6, 2012 9:53 AM
  • Hi,

    this may help

    SELECT DB_NAME() AS database_name
    ,OBJECT_NAME(a.major_id) AS table_name
    ,USER_NAME(a.grantee_principal_id) AS grantee_principal
    ,USER_NAME(a.grantor_principal_id) AS grantor_principal
    ,a.permission_name
    ,a.state_desc
    FROM sys.database_permissions a, sys.tables b
    WHERE a.major_id=b.object_id
    ORDER BY OBJECT_NAME(a.major_id)

    • Marked as answer by Iric Wen Monday, June 18, 2012 1:58 AM
    Thursday, June 7, 2012 7:50 AM
  • Thanks

    the script is very useful.!

    Monday, June 11, 2012 4:38 AM