Post reply

procedure to get all the permission of a user

  • April 30, 2009 at 2:39 am

    #131054

    Is there any command or system stored procedure available in SQL server which returns all the permissions of a sql server login with username/id as input parameter.

    please help....

  • April 30, 2009 at 3:31 am

    #986514

    Try sp_helprotect. It accepts a number of parameters one of which is @username.

    Mike

  • April 30, 2009 at 4:05 am

    #986528

    Thanks for the update mike..

    But this is not what i want. I just want to know all the rights granted to the user like create database or create table, etc when ID/Name is passed.

    Thanks....

  • April 30, 2009 at 6:25 am

    #986607

    But this is not what i want. I just want to know all the rights granted to the user like create database or create table, etc when ID/Name is passed.

    Rights are granted to ROLES and Roles are mapped to Logins.

    Please check the below query.

    SELECTSDP.state_desc, SDP.permission_name, SSU.[name] AS 'Schema', SSO.[name], SSO.[type]

    FROMsys.sysobjects SSO

    INNER JOIN sys.database_permissions SDP ON SSO.id = SDP.major_id

    INNER JOIN sys.sysusers SSU ON SSO.uid = SSU.uid

    ORDER BY SSU.[name], SSO.[name]

  • April 30, 2009 at 8:46 am

    #986695

    Have a look at using sys.fn_my_permissions, e.g.

    execute as user = 'put your username here'

    select*

    fromsys.fn_my_permissions(NULL,'DATABASE')

    revert

    go

    This link may help:

    http://msdn.microsoft.com/en-us/library/ms176097.aspx

  • April 30, 2009 at 11:41 am

    #986816

    sreedevi.balan (4/30/2009)

    Thanks for the update mike..

    But this is not what i want. I just want to know all the rights granted to the user like create database or create table, etc when ID/Name is passed.

    Thanks....

    It looks like you want to see both server and database permissions for a login. I've used a modified version of this script when migrating logins and databases and it works pretty well to show server roles, database roles, and object permissions:http://www.sqlservercentral.com/scripts/31776/

    BTW, a LOGIN is a servre level security principal and a USER is a database level security principal. In your case, it seems you want info about both.

    Greg

  • May 3, 2009 at 11:33 pm

    #987928

    thanks a lot.... this function really helped 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

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