Determine Permissions on All Stored Procedures RRS feed

  • Question

  • I have two sql servers N and O, (new and old). I have restored a few databases on server N.  I want to make sure persmissions are the same on the all the stored procedures.  I am right clicking the stored procedure, selecting properties and clicking permissions.  Then i do that on server O and compare the two to verify they are the same.  Is there a way to query the Users or Roles and permissions which have been granted?  This way i might have a list to verify rather than all this clicking!  Any help would be appreciated. 



    joe - not a plumber
    Monday, April 26, 2010 4:11 PM


  • To determins the permission at Server Level ;

    [srvprin].[name] [server_principal]
    [srvprin].[type_desc] [principal_type]
    [sys].[server_permissions] srvperm
    [sys].[server_principals] srvprin
    ON [srvperm].[grantee_principal_id] 
    WHERE [srvprin].[type] IN ('S''U''G'
    ORDER BY [server_principal][permission_name]

    To determine the permission at Object level;



    U.name, O.name, permission_name , Grantor = suser_name(grantor_principal_id)
    from sys.database_permissions
    join sys.sysusers U on grantee_principal_id = uid
    join sys.sysobjects O on major_id = id
    order by U.name


    Sivaprasad S http://sivasql.blogspot.com Please click the Mark as Answer button if a post solves your problem!
    Tuesday, April 27, 2010 12:39 AM