none
how to get a user's permission using T-SQL

    Question

  • hi, group,

     

    Does anyone know how to get a user's permission using T-SQL?

     

    Thanks.

    Sunday, September 21, 2008 8:13 AM

Answers

  • You can do something like this:

     

    -- (1) Change to the users's security context:

     

    EXECUTE AS USER = 'InsertUserNameHere';

     

    -- (2) Use the system function 'fn_my_permissions'


    SELECT * FROM fn_my_permissions(NULL, 'DATABASE')

    Refer to Books Online, Topic: fn_my_permissions for more details...
    Sunday, September 21, 2008 2:09 PM
    Moderator

All replies

  • You can do something like this:

     

    -- (1) Change to the users's security context:

     

    EXECUTE AS USER = 'InsertUserNameHere';

     

    -- (2) Use the system function 'fn_my_permissions'


    SELECT * FROM fn_my_permissions(NULL, 'DATABASE')

    Refer to Books Online, Topic: fn_my_permissions for more details...
    Sunday, September 21, 2008 2:09 PM
    Moderator
  • If you use sql server 2005/2008,you can get the user's permission like this:

     

     

    Code Snippet

    use [db name]
    go

    select
     a.*,
     b.name
    from sys.database_permissions a
    inner join sys.database_principals b
     on a.grantee_principal_id = b.principal_id
      and b.name = 'user name'

     

     

     

    Hope this helps.
    Sunday, September 21, 2008 2:26 PM
    Moderator

  • SELECT [UserName] = ulogin.[name],
           [UserType]             = CASE princ.[type]
                             WHEN 'S' THEN 'SQL User'
                             WHEN 'U' THEN 'Windows User'
                             WHEN 'G' THEN 'Windows Group'
                        END,
           [DatabaseUserName]     = princ.[name],
           [Role]                 = NULL,
           [PermissionState]      = perm.[state_desc],
           [PermissionType]       = perm.[permission_name],
           [ObjectType]           = CASE perm.[class]
                               WHEN 1 THEN obj.type_desc -- Schema-contained objects
                               ELSE perm.[class_desc] -- Higher-level objects
                          END,
           [ObjectName]           = CASE perm.[class]
                               WHEN 1 THEN OBJECT_NAME(perm.major_id) -- General objects
                               WHEN 3 THEN schem.[name] -- Schemas
                               WHEN 4 THEN imp.[name] -- Impersonations
                          END,
           [ColumnName]           = col.[name]
    FROM   --database user
           sys.database_principals princ
           LEFT JOIN --Login accounts
                sys.server_principals ulogin
                ON  princ.[sid] = ulogin.[sid]
           LEFT JOIN --Permissions
                sys.database_permissions perm
                ON  perm.[grantee_principal_id] = princ.[principal_id]
           LEFT JOIN --Table columns
                sys.columns col
                ON  col.[object_id] = perm.major_id
                AND col.[column_id] = perm.[minor_id]
           LEFT JOIN sys.objects obj
                ON  perm.[major_id] = obj.[object_id]
           LEFT JOIN sys.schemas schem
                ON  schem.[schema_id] = perm.[major_id]
           LEFT JOIN sys.database_principals imp
                ON  imp.[principal_id] = perm.[major_id]
    WHERE  princ.[type] IN ('S', 'U', 'G')
           AND -- No need for these system accounts
               princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')
    ORDER BY
           ulogin.[name],
           [UserType],
           [DatabaseUserName],
           [Role],
           [PermissionState],
           [PermissionType],
           [ObjectType],
           [ObjectName],
           [ColumnName]
          
    Friday, December 07, 2012 5:12 PM