locked
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. 

    thanks.

    joe.


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

Answers

  • To determins the permission at Server Level ;

    SELECT 
      
    [srvprin].[name] [server_principal]
    ,
      
    [srvprin].[type_desc] [principal_type]
    ,
      
    [srvperm].[permission_name]
    ,
      
    [srvperm].[state_desc] 
    FROM 
    [sys].[server_permissions] srvperm
      
    INNER JOIN 
    [sys].[server_principals] srvprin
        
    ON [srvperm].[grantee_principal_id] 
    [srvprin].[principal_id]
    WHERE [srvprin].[type] IN ('S''U''G'
    )
    ORDER BY [server_principal][permission_name]
    ;

    To determine the permission at Object level;

    select

     

    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