Stored Procedure to assign permissions

This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.

/*-------------------------------------------------------------------------- Automatically assign the role permissions --------------------------------------------------------------------------*/USE DatabaseNameSET NOCOUNT ONDECLARE @objName varchar(80)DECLARE @objType char(2)DECLARE @username varchar(100)SET @username = 'UserNameToAssignPermissionsTo'DECLARE grant_perms_on_sps CURSOR FORSELECT name, typeFROM SYSOBJECTS WHERE   (     (type = 'P')OR     (type = 'FN')OR     (type = 'TF')OR     (type = 'U')OR     (type = 'V')   )AND   uid = 1AND   status > -1ANDLEFT(name, 3) <> 'dt_'--See Note 1OPEN grant_perms_on_spsFETCH NEXT FROM grant_perms_on_sps INTO @objName, @objTypeWHILE@@FETCH_STATUS = 0BEGIN  IF @objType = 'P'OR @objType = 'FN'BEGIN    EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO ' + @username)    PRINT ('GRANTED EXECUTE ON dbo.' + @objName + ' TO ' + @username)  END  IF @objType = 'TF'  BEGIN    EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO ' + @username)    PRINT ('GRANTED SELECT ON dbo.' + @objName + ' TO ' + @username)  END  FETCH NEXT FROM grant_perms_on_sps   INTO @objName, @objTypeENDCLOSE grant_perms_on_spsDEALLOCATE grant_perms_on_spsGO------------------------------------------------------------------------

Note 1: In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch:

DECLARE @prefix varchar(100)SET @prefix = 'PrefixToUse'LEFT(name, LEN(@prefix)) = @prefix