Post reply

SQL Server logins

  • July 21, 2008 at 4:00 am

    #125017

    Hi, how to find the permissions of a login in a database sql server 2005. any query to find that??

  • July 21, 2008 at 4:20 am

    #845633

    Check for the system table sys.fn_my_permissions OR sys.fn_builtin_permissions

    SELECT * FROM sys.fn_my_permissions( NULL,'database' )

    --Ramesh

  • July 21, 2008 at 4:57 am

    #845664

    Ramesh (7/21/2008)

    Check for the system table sys.fn_my_permissions OR sys.fn_builtin_permissions

    SELECT * FROM sys.fn_my_permissions( NULL,'database' )

    thanks for the quick reply..however when I excuted the above script, I didnt get any records. moreover I would like to know if we have any script that could be executed against a login. and that script will give the properties of that login.like select permissions etc...

  • July 21, 2008 at 5:07 am

    #845670

    For server-level permissions, you can query sys.server_permissions and sys.server_principals thusly

    SELECT NAME, permission_name, state_desc

    FROM sys.server_permissions sp

    INNER JOIN sys.server_principals pri ON sp.grantee_principal_id = pri.principal_id

    For database-level permissions, you can do much the same with sys.database_permissions and sys.database_principals. Onlyy valid for the current DB

    SELECT NAME, permission_name, class_desc , OBJECT_NAME (major_id) , state_desc

    FROM sys.database_permissions dp

    INNER JOIN sys.database_principals pri ON dp.grantee_principal_id = pri.principal_id

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 21, 2008 at 5:23 am

    #845682

    GilaMonster (7/21/2008)

    For server-level permissions, you can query sys.server_permissions and sys.server_principals thusly

    SELECT NAME, permission_name, state_desc

    FROM sys.server_permissions sp

    INNER JOIN sys.server_principals pri ON sp.grantee_principal_id = pri.principal_id

    For database-level permissions, you can do much the same with sys.database_permissions and sys.database_principals. Onlyy valid for the current DB

    SELECT NAME, permission_name, class_desc , OBJECT_NAME (major_id) , state_desc

    FROM sys.database_permissions dp

    INNER JOIN sys.database_principals pri ON dp.grantee_principal_id = pri.principal_id

    thanks, but think this is the same result as sp_helprotect..however I want a script that could be ran against a particular login. the result will given the properties of only that login!

  • July 22, 2008 at 2:27 am

    #846217

    Try :

    sp_helplogins 'LoginName'

  • July 22, 2008 at 2:42 am

    #846221

    vanessab (7/22/2008)

    Try :

    sp_helplogins 'LoginName'

    thats true..you could also use :

    sp_helpuser youruserid

    🙂

  • July 22, 2008 at 3:12 am

    #846238

    rinu philip (7/22/2008)

    vanessab (7/22/2008)

    Try :

    sp_helplogins 'LoginName'

    thats true..you could also use :

    sp_helpuser youruserid

    🙂

    Thanks aton rinu..it worked ...koooolll

  • July 22, 2008 at 3:29 am

    #846248

    Most Welcome:D

  • July 23, 2008 at 5:35 am

    #846929

    You can also query the database system views if a user has restrictive permissions

    INFORMATION_SCHEMA.COLUMN_PRIVILEGES

    INFORMATION_SCHEMA.table_privileges

    Ezekiel

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply